sumproduct or countif
 

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.

sumproduct or countif
 

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))

--
Biff
Microsoft Excel MVP


"freebee" wrote in message
...
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.

sumproduct or countif
 

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

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

"freebee" wrote:

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.

sumproduct or countif
 

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


"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"freebee" wrote in message
...
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.

sumproduct or countif
 

The first one did not work

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

--
Biff
Microsoft Excel MVP


"freebee" wrote in message
...
Hi,
The first one did not work, but the 2nd one worked, thank you so much.


"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"freebee" wrote in message
...
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.

sumproduct or countif
 

Try it like this:

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

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
=SUMPRODUCT(--(A1:A10="a")+--(A1:A10="b"),B1:B10)

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

"freebee" wrote:

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.

sumproduct or countif
 

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 D1:D3
The you can use the formula

=DSUM(O19:Q79,3,D1:D3)

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.

--
Thanks,
Shane Devenshire


"freebee" wrote:

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.

sumproduct or countif
 

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.
--
David Biddulph


"JMay" wrote in message
...
=SUMPRODUCT(--(A1:A10="a")+--(A1:A10="b"),B1:B10)

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

"freebee" wrote:

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.