Search for specific text within a cell that contains a lot of other text within array formula

[polardude1983]

Hello All,

I have always appreciated the help that everyone has given here to other people and can usually find the answer that I'm looking for. You are all an amazing community.

What I'm trying to do and I got most of it is find how many "occurrences" an employee has had within the past 30 days. The formula that I have below works but only counts the cells that exactly match C2 and not *C2*.

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ARRAYFORMULA[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]SUM[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]TODAY[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]B5:B[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]30[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]*[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]C5:C[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]C2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]*[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]A5:A[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Kathleen"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])

C2 = "Time and Temp"

With the above formula it calculates that the employee Kathleen has had 3 Time and Temp occurences in the last 30 days. But it should actually be 4. But it's not recognizing the occurence with Time and Temp and goal sheet which would be cell C8.

I have tried using ISNUMBER or SEARCH or Find or combining them or using "*"&C2 but when I do it either counts 0 instances or just comes up as a bad formula.

Here is what I have as my example

[/FONT][/COLOR][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Kathleen[/TD]
[TD]4/9/2019[/TD]
[TD]Time and Temp[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jim[/TD]
[TD]4/10/2019[/TD]
[TD]Time and Temp[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Kathleen[/TD]
[TD]4/10/2019[/TD]
[TD]Time and Temp[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Kathleen[/TD]
[TD]5/4/2019[/TD]
[TD]Time and Temp
Goal Sheet[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Jim[/TD]
[TD]5/6/2019[/TD]
[TD]Tool 2
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Kathleen[/TD]
[TD]5/8/2019[/TD]
[TD]Time and Temp[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jim[/TD]
[TD]5/9/2019[/TD]
[TD]Time and Temp[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Jim[/TD]
[TD]5/10/2019[/TD]
[TD]Time and Temp[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to MrExcel!

Based on your formula, I assume you're actually working on Google sheets. This is the kind of thing that would be useful to include in your question. Without that caveat, whatever solution someone comes up with might not work for you. In Excel, I might recommend:

=COUNTIFS(A:A,"Kathleen",B:B,">="&TODAY()-30,C:C,"*Time and Temp*")

But I don't know if it works in Google.

 

[DanteAmor]

Try this

=SUMPRODUCT((A5:A12="Kathleen")*((TODAY()-B5:B12<=30)+0)*(ISNUMBER((FIND(C2,C5:C12)))))

 

[DanteAmor]

Also try with this array formula
To accept press Shift + Control + Enter


{=COUNT(IF(A5:A12="Kathleen",IF(TODAY()-B5:B12<=30,IF(ISNUMBER(FIND(C2,C5:C12)),1))))}

 

[polardude1983]

Try this

=SUMPRODUCT((A5:A12="Kathleen")*((TODAY()-B5:B12<=30)+0)*(ISNUMBER((FIND(C2,C5:C12)))))

This worked and thank you.

And also Eric W you are correct I am working in Google Sheets. I did forget that as I was trying to remember everything to say I did forget one thing. For the code that you gave in Google sheets I tried that but it comes up as just 0 as the output. But nevertheless thank you for your reading this and helping out.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.