Hi, I am trying to use the countifs function to count the total number of times i can see both the word "sales" and "marketing" in a list. The function i've typed in is =countifs(A1:A12,"sales",A1:A12,"marketing"). The result I get is zero. When I've tried using this function with one or the other (sales or marketing but not both), I get a result. Any suggestions? Many thanks!

Hi,

A cell can't contain the exact value "sales" and the exact value "marketing", so your COUNTIFS() formula returns 0.

Assuming you want to count the cells in A1:A12 which are "sales" OR "marketing" you can do it like this:

=COUNTIF(A1:A12,"sales")+COUNTIF(A1:A12,"marketing ")

or

=SUM(COUNTIF(A1:A12,{"sales","marketing"}))

or

=SUMPRODUCT(COUNTIF(A1:A12,B1:B2))
where B1 has the word sales and B2 has the word marketing

__________________
Colin

RAD Excel Blog