Count function with exclusions

Hi all,

I am trying to setup a formula that does the following:

Sheet 1 contains the formula. It performs a COUNT on a column on Sheet 2 and dislpays the result on sheet 1. I need to tell Excel to omit from the COUNT list, all entries containing the word "Free".

Inm other words, i need a count performed on column A of sheet 2 but the result must ignore all entries within that column A that contain the word Free in it.

Is this possible?

Please advise.

Many thanks

D

Hi,

=COUNTIF(Sheet2!A1:A10,"<>"&"Free")

HTH
Carim

wow, very quick reply. And it works excellently too.

Million thanks Carim.

D ;-)

Thanks for the feedback

Carim

Carim,

I've realised soemthing. Its not entirely wokring properly now. If i use your command, i get a value. But if i go to the other worksheet and add a "valid" number, the sum on Sheet 1 doesn't increment. It was working b4, but its not adding to the count if i add more entries on Sheet 2. Why would this be, if before it was working fine?

Please advise.

ok, what seems to be happening is that if i enter a numerical value, the COUNTIF figures increases. Thats good. However, if i add any sort of word (text) even if nothing near the word "Free", the counter remains still. I thought the code you gave me would only filter out the keywird Free?

Plase advise.

Thanks

D

Originally Posted by de049

Hi all,

I am trying to setup a formula that does the following:

Sheet 1 contains the formula. It performs a COUNT on a column on Sheet 2 and dislpays the result on sheet 1. I need to tell Excel to omit from the COUNT list, all entries containing the word "Free".

Inm other words, i need a count performed on column A of sheet 2 but the result must ignore all entries within that column A that contain the word Free in it.

Is this possible?

Please advise.

Many thanks

D

=COUNTIF(A1:A7,"<>*free*")
or
=SUMPRODUCT(--NOT(ISNUMBER(SEARCH("free",A1:A7))))