Sumifs

[Cute_Petals]

Hi,

Can anyone tell me what's wrong with my formula? I tried to use sumifs to sum the amount if the Fees is equal to "Bank" or "FX". It works if i just do one argument but not two. Please help!

[Tony Valko]

One way:

=SUM(SUMIF(A:A,{"Bank","FX"},B:B))

[Cute_Petals]

Thanks! Could you explain why it has to add sum up front and when should the {} be used?

[Pete_UK]

Try it like this:

=SUMIFS(B:B,A:A,"Bank")+SUMIFS(B:B,A:A,"FX")

or like this:

=SUMIF(A:A,"Bank",B:B)+SUMIF(A:A,"FX",B:B)

SUMIFS ANDs the conditions together, so they all have to be satisfied.

Hope this helps.

Pete

[Tony Valko]

By itself, the SUMIF(S) function(s) can only accept a single criteria per range.

In your application you need 2 criteria in one range. So, we add the SUM function which in effect is doing this:

=SUM(SUMIF(A:A,"Bank",B:B),SUMIF(A:A,"FX",B:B))

You can use cells to hold the criteria:

D1 = Bank
D2 = FX

Then use this version:

=SUMPRODUCT(SUMIF(A:A,D1:D2,B:B))