countif using cell refrences within a string

A

alistair01

hi
I am trying to use a count if to count the number of times certain tex
appears in a data sheet. eg

=COUNTIF(atl!J:J,"*Program 'Panic' - triggered by user*")

this works fine, however to make this app more user friendly i want t
be able to make the text part of look at a a cell instead of th
'Panic' part so i want it to look like this

=COUNTIF(atl!J:J,"*Program '("a2")' - triggered by user*")

Where a2 is the cell refernece.

Thanks in advance
 
A

alistair01

That works great, but i am trying to another condition to the statement
Usually i use the sum function:

=SUM(IF(atl!$L$2:$L$632=1,IF(atl!$N$2:$N$632=3,1,0)))

entered as an array formula but wen i enter this:

=SUM(IF(atl!$N$2:$N$632=1,IF(atl!J:J,"*'"&A105&"'-cleared b
user*",1)))

All i get is #NUM! ( this is entered as an array as well).

Totally stuck!
Thanks in advanc
 
Top