CountA question
 

Hi there,

In cells R18 to R20 different text can be entered by users. I need to count
the number of cells with text (sometimes cells are blank so are not counted)
but also exclude certain possible text options which are listed in cells R941
ot R946. I currently have COUNTA(R18:R20) but am having trouble adding the
'excluding' part of the formulae. Can someone please help! Thank you.

CountA question
 

=SUMPRODUCT(--(R18:R20<""),--(NOT(ISNUMBER(MATCH(R18:R20,R941:R946,0)))))

--
__________________________________
HTH

Bob

"Andrew" wrote in message
...
Hi there,

In cells R18 to R20 different text can be entered by users. I need to
count
the number of cells with text (sometimes cells are blank so are not
counted)
but also exclude certain possible text options which are listed in cells
R941
ot R946. I currently have COUNTA(R18:R20) but am having trouble adding the
'excluding' part of the formulae. Can someone please help! Thank you.

CountA question
 

Thanks Bob, that is great :)

"Bob Phillips" wrote:

=SUMPRODUCT(--(R18:R20<""),--(NOT(ISNUMBER(MATCH(R18:R20,R941:R946,0)))))

--
__________________________________
HTH

Bob

"Andrew" wrote in message
...
Hi there,

In cells R18 to R20 different text can be entered by users. I need to
count
the number of cells with text (sometimes cells are blank so are not
counted)
but also exclude certain possible text options which are listed in cells
R941
ot R946. I currently have COUNTA(R18:R20) but am having trouble adding the
'excluding' part of the formulae. Can someone please help! Thank you.