COUNT IF x 2

  • Thread starter Thread starter James
  • Start date Start date
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
 
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
 
Hi Pascal,

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

Thanks,

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

Cordially
Pascal
 
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
 
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
 
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
 
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...
 
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...
 
Back
Top