sumproduct or countif

[freebee]

Hi, I need to sum numbers in one column based on 2 critarias from another
column, I have formula as
=SUMPRODUCT(--($O$20:$O$79="a"),--($O$20:$O$79="b"),$Q$20:$Q$79)
but the result showed 0.
Am I correct for using sumproduct or shall I use countif (which I haven't
learned yet)?
Thank you.

 

[T. Valko]

So, the criteria is: O20:O79 = A *OR* B

Try one of these:

=SUMPRODUCT((O20:O79={"a","b"})*Q20:Q79)

=SUM(SUMIF(O20:O79,{"a","b"},Q20:Q79))

 

[JMay]

=SUMPRODUCT(--(A1:A10="a")+--(A1:A10="b"),B1:B10)

change column letters row numbers according to your set up..

 

[freebee]

Hi,
The first one did not work, but the 2nd one worked, thank you so much.

 

[T. Valko]

The first one did not work

You must have some entries other than numbers in the sum range if it didn't
work.

 

[T. Valko]

Try it like this:

=SUMPRODUCT((A1:A10="a")+(A1:A10="b"),B1:B10)

 

[ShaneDevenshire]

Hi,

A totally different idea - suppose there are titles on row 19, directly
above the data, and suppose the one in O19 is "Letter". In a separate range
enter
Letter
a
b

Suppose this is in D13
The you can use the formula

=DSUM(O19:Q79,3,D13)

The D-functions are under employed because they require a criteria range
somewhere else in the spreadsheet. But they do produce simple easy to
understand formulas regardless of how complex the criterial.

 

[David Biddulph]

You don't need the double unary minus if you've got the addition. The
arithmetic operation is enough to coerce the conversion from boolean to
number. Double unary minus (or double unary negation if you prefer) is just
a way of forcing an arithmetic operation where you don't already have one.
You'll see that if you use the SUMPRODUCT((condition1)*(condition2)) format
you don't need the double unary minus, but with
SUMPRODUCT(--(condition1),--(condition2)) [comma instead of multiply] you do
need it.