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,



"Jarek Kujawa" wrote:

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