SUMIFS Function

Hello everyone

I have a question about this function. Does the second criteria range has to be different to the first one?

=SUMIFS(C:C;D:D;"Zloty";D:D;"GBP")

I am using this one where I have all the amounts in C:C (Sum range) then in D:D I have the currency.


PS I know it does not make any sense to add up zlotys and pounds together

Thank you

Maybe

Formula:

Please Login or Register  to view this content.

Thank you very much, the first one returns spill error tho.

Btw what does the - wildcard mean?

Many thanks,

Could you kindly explain it in words?

Why are the criteria1 and criteria range 2 in matrix form?

Cheers

Hi viewotst,

Double negative (--) means change to true/false. I don't know what's causing the spill error but here is the logic behind the formula:

New formula(didn't test the 1st one which cause erroneous result, has nothing to do with spill error):

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

1) double negative (--): Means True/False or 0/1

So in the picture --(D:D="Zloty") would show: {1,0,0,0,0}
Likewise --(D:D="GBP") would show: {0,0,1,0,0}

2)Simple multiplication

--(D:D="GBP")*(C:C) means: {0*1,0*2,1*3,0*4,0*5} which gets you {0,0,3,0,0}
--(D:D="Zloty")*(C:C) means: {1*1,0*0,0*0,0*0,0*0} gets you {1,0,0,0,0}

3)Last add the 2 condition. SUMPRODUCT(--(D:D="Zloty")*(C:C) + --(D:D="GBP")*(C:C)): {1+0,0+0,0+3,0+0,0+0} = 4

4)To add conversion, multiply conv. formula beside the one you want to convert.

SUMPRODUCT(--(D:D="Zloty")*(C:C)*(.19/1.00) + --(D:D="GBP")*(C:C))