CODEWITHSUNDEEP
excel-formula
Pol Lanski
Pol Lanski

Reputation: 11

SUM(SUMIF... doesn't return the expected result?

G'day,
I have rows of different operations

I want to find the progressive total all USD operations in column E and the progressive total of EUR operations in column F.

I used the formula:
=SUM(SUMIF(A1;{"SELL USD";"BUY USD"};$D$1:D1))

But it's returning always the amount in D1 if the operation is a USD one and returning 0 if the operation is a EUR one.

Sincerely, I don't even know if this is the formula I'm looking for because I don't seem to be able to make it work properly.

Also, I might not be explaining myself properly so I'm more than happy to provide more info.

Thank you in advance

Upvotes: 1

Views: 154

Answers (1)

AnotherParker
AnotherParker

Reputation: 790

That's not the right way to call SUMIF(). It takes ranges in the spreadsheet and a single condition, not array constants.

It would probably be better to use SUMPRODUCT():

=SUMPRODUCT(--($A$1:A1="SELL USD")--($A$1:A1="BUY USD"),$D$1:D1)

the first input to SUMPRODUCT will evaluate to 1 if the cell in column A is "SELL USD" or "BUY USD". The second input is column D. SUMPRODUCT will multiply these and take the sum.

EXPLANATION:

The "--" is a shorthand for "convert this expression to 1 if it's true, or 0 if it's false". Putting them next to each other adds the values together. Obviously only one of them will be true, so the result is 1 if the cell in the range $A$1:A1 is either SELL USD or BUY USD, and 0 otherwise. This is then multiplied by the value in column D, and all the values are added together.

Google SUMPRODUCT and you'll find lots of tutorials for instance http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

Upvotes: 1

Related Questions