Cell contains text - count by multiple criteria

[andibevan]

I am using the following formula to count the number of entries i
column E which CONTAIN (i.e. within other text in each cell) the tex
"ACT"

=Countif(e8:e508,"*ACT*")

How would I then count all the entries that *contain* "ACT" in column
(rows 5-508) and that also equals "RSK" in *equals* column A (row
5-508)?

I have taken a look at arrays and other things but can't manage to ge
anywhere.

Thanks

Andy

 

[JulieD]

Hi

one way
=SUMPRODUCT((E8:E508=F10)+(A1:A501=G10))
where F10 has *ACT*
and G10 has RSK

Cheers
JulieD

 

[Guest]

Unfortunately, you will not be able to put the totals into
the same cell. You will need to create another Countif for
RSK, then you can SUM them together in a 3rd cell.

 

[andibevan]

Julie,

I have tried that but it only counts the number of items that are RSK.
It produces a result of 55 - when I manually count the items containin
ACT there are only 8 - any ideas?


Anonymous,

I don't wish to put two numbers in the same cell - I need the number o
items that contain both ACT in e5:e508 and equal RSK in column A, sorr
if I was unclear

Thanks

And

 

[Harlan Grove]

one way
=SUMPRODUCT((E8:E508=F10)+(A1:A501=G10))
where F10 has *ACT*
and G10 has RSK

....

Since when does Excel's = operator support the * wildcard in text
comparisons? Also, this will double count any instances in which both
conditions are satisfied.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(ISNUMBER(SEARCH("ACT",E5:E508))),--(A5:A508="RSK"))

 

[JulieD]

Hi Harlan

didn't seem to do that when i first tested it out - but you know more about
this than i do, so i'm going back again to re-test it.

Thanks
JulieD