Countif & Sumif with Multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Kim said:
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.
Click to expand...

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.
Click to expand...

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
 
Kim Shelton at PDC said:
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.
Click to expand...


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.
Click to expand...


SUMIF doesn't work with multiple criteria, you need SUMPRODUCT here.
 
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
 
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)
 
Back
Top