COUNTIF with multiple text criteria

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
 
P

Pete_UK

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
 
S

Shane Devenshire

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.
 
F

francis

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
 
R

Richard

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)
 
R

Richard

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
 
J

Jarek Kujawa

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

=SUMPRODUCT((NOT($A$1:$A$100="Word1"))*($B$1:$B$100="Word1"))
 
J

Jarek Kujawa

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
 
R

Richard

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
 
P

P$

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top