COUNT IF x 2

J

James

Hello everyone,

I'm using the following formula to count the number of occurences in a list
:

=COUNTIF('Full Output'!K:K,"*Outlook Edition*")

I however need to check another argument in that 'Full Output'!C:C must =
"Sales".

I would therefore like to say COUNTIF('Full Output'!K:K,"*Outlook Edition*"
AND 'Full Output'!C:C, "Sales")) but can't seem to check for both.

Thanks very much for your help,

James
 
P

papou

Hi James
COUNTIF will only work for one criteria.
Use SUMPRODUCT instead:
=SUMPRODUCT(('Full Output'!K:K="*Outlook Edition*")*('Full Output'!C:C,
"Sales"))

HTH
Cordially
Pascal
 
J

James

Hi Pascal,

thanks for your reply. This doesn't seem to wrok though. Maybe I'm going
wrong somewhere.

Thanks,

James
 
P

papou

Please amend:
=SUMPRODUCT(('Full Output'!K:K="*Outlook Edition*")*('Full
Output'!C:C="Sales"))

Cordially
Pascal
 
J

James

Thanks again for your reply. The formula just seems to return #NUM though.

Here is a snapshot of part of my table :

Department Type Question09
Sales Outlook Edition, Office Edition
Sales Intellisync
CSS Don't know what these are
CSS na
CSS Office Edition
CSS Don't know what these are
CSS Don't know what these are
CSS Don't know what these are
CSS Don't know what these are
CSS Don't know what these are
Sales Intellisync, Outlook Edition
Sales Intellisync, Outlook Edition
Sales Don't know what these are


although it has more columns, I am trying to count the number of times
"Outlook Edition" appears. I wanted to use a pivot table but cannot as
users were able to choose from a list multiple entries that were seperated
by "," in the output file.

Thanks,

James
 
J

Jerry W. Lewis

SUMPRODUCT (and other formulas that do array processing) cannot operate
on entire columns. Try

=SUMPRODUCT(('Full Output'!K1:K65535="*Outlook Edition*")*('Full
Output'!C1:C65535="Sales"))

Also, unlike COUNTIF, a logical expression like
('Full Output'!K1:K65535="*Outlook Edition*")
within SUMPRODUCT does not interpret asterisks as wildcards, so you may
need to expand to

=SUMPRODUCT(ISNUMBER(FIND("Outlook Edition",'Full
Output'!K1:K65535))*('Full Output'!C1:C65535="Sales"))

Jerry
Excel MVP
 
P

papou

James
Ok I have been testing and I've just realised that the use of the entire
column as address might be the reason why it does not work.
Also I am not sure you can use wildcards (* and ?)

Cordially
Pascal
 
J

James

ok, thanks Pascal

papou said:
James
Ok I have been testing and I've just realised that the use of the entire
column as address might be the reason why it does not work.
Also I am not sure you can use wildcards (* and ?)

Cordially
Pascal

in
Click to expand...
 
J

James

thanks Jerry, this works great.

James
Jerry W. Lewis said:
SUMPRODUCT (and other formulas that do array processing) cannot operate
on entire columns. Try

=SUMPRODUCT(('Full Output'!K1:K65535="*Outlook Edition*")*('Full
Output'!C1:C65535="Sales"))

Also, unlike COUNTIF, a logical expression like
('Full Output'!K1:K65535="*Outlook Edition*")
within SUMPRODUCT does not interpret asterisks as wildcards, so you may
need to expand to

=SUMPRODUCT(ISNUMBER(FIND("Outlook Edition",'Full
Output'!K1:K65535))*('Full Output'!C1:C65535="Sales"))

Jerry
Excel MVP
Click to expand...
 
Top