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

On Mar 2, 4:15*pm, Richard wrote:
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

=SUMPRODUCT((A1:A100="word1")*(B1:B100="word2"))*1 0
and see:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for the "rest of the story"
--
Gary''s Student - gsnu2007L


"Richard" wrote:

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

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.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Richard" wrote:

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

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





"Richard" wrote:

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

Many thanks for your lightning responses guys.


=SUMPRODUCT((A1:A100="word1")*(B1:B100="word2"))*1 0 - 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)



On 2 Mar, 16:55, francis wrote:
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

"Richard" wrote:
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

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

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




Uzytkownik "Richard" napisal w wiadomosci
...
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
another way
=SUMPRODUCT(($A$1:$A$100<"Word1"))*($B$1:$B$100=" Word1"))

And another to count occurances of Word1 in Column C, but only if it
is NOT in Column B or Column A.
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<"Wor d1"))*($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<"Wo rd1"))*($C$1:$C$100="Word1"),1,))}

DO NOT insert curly brackets by hand, the formula will not work



U¿ytkownik "Jarek Kujawa" napisa³ w wiadomo¶ci
...
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$10 0="Word1"))




Uzytkownik "Richard" napisal w wiadomosci
...
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