SumProduct Issue

[SteveT]

Why can i not seem to get the hang of it the condition checks itself as True before comparing with data.


=SUMPRODUCT((B7:B10)*((CELL("format",C7:C10)="C2")*1)) this gives me the sum of all data not the ones where the currency format = "C2" (us dollars)


What is the fix to my formula issue. Thanks in advance!

 

[Luke M]

Unofortunately, the CELL function asks for a "reference", not "Range". Consequently, it can only handle a single cell input, and is changing your formula to be:

B7:B10 * C7 = C2 * 1

So, if C7 is the C2 format, it's:

B7:B10 * 1 * 1 aka, SUM(B7:B10)


You'll need to use some other criteria rather than the format, or use a helper column.

 

[SteveT]

Thanks. Can the refernce be a named range?

 

[Luke M]

If the named range is only 1 cell, yes.