count with multiple criteria and a word search in one column

[nsmager]

Hi. I am trying to do a count with multiple criteria. One criteria is the year, another is the text lapse in a cell (the column is either client or lapse" and the last criteria is to count the number of times the text 5PR shows up in a column, although it may not be the only text in any of the cells.

I have tried
=COUNTIFS($Z$2:$Z$1876,$C$1929,$AA$2:$AA$1876,"lapse",$I$3:$I$1876,E1928)
If I do this with only the =COUNTIFS($Z$2:$Z$1876,$C$1929,$AA$2:$AA$1876,"lapse") the formula works, it is when I add the last piece, which is search to match the text in F1928 in the column of I.

I have also tried =SUMPRODUCT(--($Z$2:$Z$1876=$C$1929),--($AA$2:$AA$1876="lapse"),--(COUNTIF($I$3:$I$1876,"*5PR*"))) and again if I remove the last criteria it works. Lastly, I used =SUMPRODUCT(($Z$2:$Z$1876=$C$1929)*($AA$2:$AA$1876="lapse")*(COUNTIF($I$3:$I$1876,$E$1928))) this did return a value, but it is incorrect....

Help!!!

[Glenn Kennedy]

Can you post the sheet (or a small part of it)?

[Jonmo1]

Try

=COUNTIFS($Z$2:$Z$1876,$C$1929,$AA$2:$AA$1876,"lapse",$I$2:$I$1876,"*"&E1928&"*")

[Glenn Kennedy]

Oh pooh Jonmo1... Rep to you from me if that's the answer...

[nsmager]

Hi Glenn

Sure... Below is a small sample

Old Expiration Old pkg Contract yr client/lapse Client Years

4/30/2011 1PR, 5PR 2011 lapse 1
4/30/2011 1PR, 5PR, 7PR 2011 client 3
5/31/2011 GFS, UNL 2011 lapse 2
5/31/2011 1PR, 5PR, 7PR 2011 client 11
5/31/2011 UNL 2011 client 6
5/31/2011 1PR, 5PR, 7PR 2011 client 12
4/30/2012 5PR, 7PR 2012 client 8
4/30/2012 2PR, 5PR, 7PR 2012 client 10
4/30/2012 1PR, 5PR, 7PR 2012 client 8
4/30/2012 5PR, 7PR, TRD 2012 client 7
4/30/2012 5PR, 6PR 2012 lapse 10
4/30/2012 1PR, 5PR 2012 client 5

[nsmager]

Thank you. That seems to work.

[Glenn Kennedy]

It's hard, from your post, to see what is in what cells (actually, it's impossible ). Can you post a spread sheet (Go advanced, Manage attachments & select the file). But check out Jonmo1's solution - does that solve your problem?

[Jonmo1]

You're welcome.