COUNTIF with multiple text criteria

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi all

I need to count all instances of Word1 in column A, but only where
column 2 contains Word2. (and then multiply the result by a factor of
n - if that's possible?)

I've got as far as: =COUNTIF(A:A,"Word1")*10 - but it stops working
when I add the column 2 criteria.

Have tried using =SUMPRODUCT but think that is for numbers only?

Hope my requirements are possible.

thanks in advance

Richard
 
Try this:

=SUMPRODUCT((A$1:A$100="Word1")*(B$1:B$100="Word2")) * factor

Note that if you are using Excel 2003 or earlier then you can't use
full-column references with SUMPRODUCT, so adjust those given to suit
your data.

It would be better to put Word1 and Word2 in two different cells (eg
C1 and D1), then you could have this formula:

=SUMPRODUCT((A$1:A$100=C1)*(B$1:B$100=D1))* factor

The advantage is that you don't have to change the formula to check
out different words, and by having other words in columns C and D then
you can just copy the formula down.

Hope this helps.

Pete
 
Hi,

If you are using 2007:

=COUNTIFS(A:A,"Word 1",B:B,"Word 2")*Factor

or

=COUNTIFS(A:A,D1,B:B,D2)*Factor

Where Word 1 is in D1 and Word 2 is in D2.
 
you can use Sumproduct in this case

=SUMPRODUCT((A2:A10="Word1")*(B2:B10="Word2")*10

The range need to be the same for this function and you can't
use whole column in 2003.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis
 
Many thanks for your lightning responses guys.


=SUMPRODUCT((A1:A100="word1")*(B1:B100="word2"))*10 - did it for me,
plus the web link helped explain things.


Sorry Francis no yes button! - but feel free to click it for me if you
have one ;)


thanks again

Richard
(using office 2007)
 
Hello again

You guys kindly helped me with SUMPRODUCT.
The formula you helped with with was =SUMPRODUCT((A1:A100="word1")*
(B1:B100="word2"))

I now have need for a formula to count occurances of Word1 in Column
B, but only if it is NOT in column A
And another to count occurances of Word1 in Column C, but only if it
is NOT in Column B or Column A.

Tried putting a minus in place of the * but without success.


Thanks in advance

Richard
 
I now have need for a formula to count occurances of Word1 in Column
B, but only if it is NOT in column A
Click to expand...

=SUMPRODUCT((NOT($A$1:$A$100="Word1"))*($B$1:$B$100="Word1"))
 
I now have need for a formula to count occurances of Word1 in Columnanother way
hope you meant NOT in Column B AND NOT in Column A.
then
=SUMPRODUCT(($A$1:$A$100<>"Word1")*($B$1:$B$100<>"Word1")*($C$1:$C$100="Word1"))

at the moment I cannot think of a SUMPRODUCT formula to count occurances of
Word1 in Column C, but only if it
is NOT in Column B or Column A.

instead try:

=SUM(IF((($A$1:$A$100<>"Word1")+($B$1:$B$100<>"Word1"))*($C$1:$C$100="Word1"),1,))

CTRL+SHIFT+ENTER this formula instead of just using ENTER cause this is an
array-formula

if it is inserted correctly curly brackets should show up just like in this
pattern

{=SUM(IF((($A$1:$A$100<>"Word1")+($B$1:$B$100<>"Word1"))*($C$1:$C$100="Word1"),1,))}

DO NOT insert curly brackets by hand, the formula will not work
 
Many thanks for your help Jarek

I got both formulas to work as needed. It was 'NOT in Column B AND NOT
in Column A.' that I required.

thanks again

Richard
 
jarek,
I have wasted hours this morning trying to do what this discussion covered.
I wish the microsoft documentation would highlight better the
CNTL+CHIFT+ENTER criteria for an array.

You have saved my day.

Thanks much,
 
Back
Top