Creating a formula for two columns with Criteria

[Guest]

A B
1 10 20
2 10 0
3 10 -5
4 10 15
5 10 0

Alright say you have this as your data field. I know that using the SUMIF
function will give me the total of A if B > 0 (or whatever I set the criteria
to), BUT how can I get the sum of A AND B if B is greater than 0

 

[Dave Peterson]

I'd use something like:
=SUMIF(B:B,">"&0,A:A)+SUMIF(B:B,">"&0)
or
=SUMIF(B:B,">"&0,A:A)+SUMIF(B:B,">"&0,B:B)

 

[Ragdyer]

Try this:

=SUMPRODUCT((B1:B5>0)*((B1:B5)+(A1:A5)))

 

[Guest]

Slightly shorter, and more elegant

=SUMPRODUCT((B1:B5>0)*A1:B5)

 

[Ragdyer]

Yes ... more elegant!<bg>