Cell contains text - count by multiple criteria

  • Thread starter Thread starter andibevan
  • Start date Start date
A

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
 
Hi

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

Cheers
JulieD
 
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.
 
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
 
JulieD said:
one way
=SUMPRODUCT((E8:E508=F10)+(A1:A501=G10))
where F10 has *ACT*
and G10 has RSK
Click to expand...
....

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.
 
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
 
Back
Top