How to count if cell contains text from a range

[jparry6]

Hi,

I have this formula:

=COUNTIFS('RAW DATA'!$H:$H,"="&$A$1, 'RAW DATA'!$E:$E, $A7, 'RAW DATA'!$G:$G, "*U6:U28*")

In the last criteria I am trying to count if the cell contains text from a range but the result it is giving me is 0, when I know the output should be 12. And I need it to be a formula as I need to apply this formula to 30 odd people in the worksheet.

Could someone help me out?

Thanks in advance
JP

 

[Special-K99]

So count number of cells where column H contains cell A1 and column E contains A7
and...

Column G contains what looks like an embedded range "U6:U28" in a cell ?
Thats what you want to check for, ie you have cells in column G that are text and the value of them are ranges, ie
column G could contain

ABCU6:U28
U6:U28
Fred BloggsU6:U29Charlie Smith

and would return 2 since the third does not contain U6:U28 (I changed the range to U6:U29 deliberately)

Are you sure this is what you really have in column G?

 

[Aladin Akyurek]

Try...

=SUMPTODUCT(COUNTIFS('RAW DATA'!$H:$H,"="&$A$1, 'RAW DATA'!$E:$E, $A7, 'RAW DATA'!$G:$G, "*"&U6:U28&"*")

 

[jparry6]

Aladin you hero!

JP

 

[Aladin Akyurek]

Aladin you hero!

JP

Glad to help.