SUMIFS (Memory blackout)

[indygo]

Hi,

I need help with SUMIFS or equivalent option in excel.
There are two conditions. I want to sum total from D and E IF it's not 0 and IF A and B has W or L.

So result from the one below should be 30.

Previously I had this: =SUM(SUMIF(range,{"<0",">0"}))
But I want to add to requirement of A and B.... so it only sums the ones with W or L.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]W[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]W[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]W[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]L[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[Jonmo1]

So in the given example, what is your expected result?

The criteria of Not 0 is not necessary, because if you include 0 in the sum, it has no effect on the result..

Try this, which would result in 30 if that table is at the very top left of your sheet.

=SUM(SUMIF(A1:B10,{"W","L"},D1:E10))

 

[JackDanIce]

Try:

=SUMPRODUCT((--($A$1:$A$10="W")+--($A$1:$A$10="L")+--($B$1:$B$10="W")+--($B$1:$B$10="L")),$D$1:$D$10,$E$1:$E$10)

@Jonmo1 mine returns 150, suspect I've misunderstood requirement

 

[indygo]

Result in given example should be 30 not 150. First solution is working fine for me.

Thank you both for help.

 

[Jonmo1]

You're welcome.

Just to be clear on what it's doing..
If A is W or L, it sums D
If B is W or L, it sums E

So if you had a case where
A=W
B=N/A
D=5
E=5

It would only sum 5 (from D)

 

[indygo]

Yes thanks,

In my file there A and B are always together if A is empty B will always be empty too so in this case it's accurate.