Countif & Sumif with Multiple criteria

[Kim Shelton at PDC]

1. Countif with multiple criteria. I have tried sumproduct as follows...
=SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
but this gives me a result as #NUM!
The cell is set to general.
What I am counting is in column A tell me how many cells have "b" and how
many cells also have "true" in G column. I only want to count the cells that
have both.

2. Sumif with multiple criteria. I have tried several things and can't get
anything to work. It is three conditions. If column A = B and Column G =
True then sum colmn H. Column H will have $ in it.

I really need this in 1 formula if possible because I have so many of these
formulas per month to do.

Any help would be greatly appreciated.

 

[shail]

Hi Kim,

It is for SUMPRODUCT....

1st thing, sumproduct cannot be used for the whole range ie A:A you
need to define it as A1:A65535.

2nd thing, rewrite the formula as for example

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*('Jan 06'!G1:G1000))

Or count some other column

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*('Jan 06'!G1:G1000="TRUE")*('Jan
06'!H1:H1000))


Hope this helps you


Thanks

Shail

I will be back to you for your second query

 

[Dave F]

SUMPRODUCT is the correct function.

Look here for syntax: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Dave

 

[Franz Verga]

1. Countif with multiple criteria. I have tried sumproduct as
follows... =SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
but this gives me a result as #NUM!
The cell is set to general.
What I am counting is in column A tell me how many cells have "b" and
how many cells also have "true" in G column. I only want to count
the cells that have both.

Hi Kim,

the SUMPRODUCT function can't manage a full column/row so you have to use
something like this:

=SUMPRODUCT(('Jan 06'!A2:A1000="B")*('Jan 06'!G2:G1000="TRUE"))

2. Sumif with multiple criteria. I have tried several things and
can't get anything to work. It is three conditions. If column A = B
and Column G = True then sum colmn H. Column H will have $ in it.

try this:

=SUMPRODUCT(('Jan 06'!A2:A1000="B")*('Jan 06'!G2:G1000="TRUE"),'Jan
06'!H2:H1000)


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Bob Phillips]

1. Countif with multiple criteria. I have tried sumproduct as follows...
=SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
but this gives me a result as #NUM!
The cell is set to general.
What I am counting is in column A tell me how many cells have "b" and how
many cells also have "true" in G column. I only want to count the cells that
have both.

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

2. Sumif with multiple criteria. I have tried several things and can't get
anything to work. It is three conditions. If column A = B and Column G =
True then sum colmn H. Column H will have $ in it.

I really need this in 1 formula if possible because I have so many of these
formulas per month to do.

SUMIF doesn't work with multiple criteria, you need SUMPRODUCT here.

 

[shail]

Hi Kim,

Sorry for the previous post. I made a mistake there.

To count you can use the formula as below

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*(Jan 06'!G1:G1000=TRUE))

Also remember "TRUE" is a keyword so it cann't be used under double
quotes.


2nd query for the sum using multiple criteria

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*('Jan 06'!G1:G1000=TRUE)*('Jan
06'!H1:H1000))


Hope this helps you

thanks
Shail

 

[Bob Phillips]

You missed an apostrophe in the second condition, and you don't need to test
for TRUE, because that is testing TRUE or FALSE = TRUE, it will return the
same value it already had

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*('Jan 06'!G1:G1000))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)