SUMIF need to look for non-specific text

[Tristram_ZX81]

I'm using Excel 365. I've been using formula like the one below to count information. So it finds any row with American Football written in the J column that kicks off between 21:01 and 22:00 inthe I column and repeats the corresponding number in the C column.

=SUMIFS('PBi LMS Data'!C4:C1000, 'PBi LMS Data'!I4:I1000, ">=21:01:00", 'PBi LMS Data'!I4:I1000, "<=22:00:00", 'PBi LMS Data'!F4:F1000, "Saturday*", 'PBi LMS Data'!J4:J1000, "American Football*")

I've tried to do the same with the below but I need to alter it so it looks for any instance of the two words 'Zona Gol' rather than a cells that only contains those two words (so for example I need it to count that row even if the L column says 2022 Zona Gol May). Does anyone know how I'd update that please?

#SUMIFS('PBi LMS Data'!C4:C1000, 'PBi LMS Data'!I4:I1000, ">#21:01:00", 'PBi LMS Data'!I4:I1000, "<#22:00:00", 'PBi LMS Data'!F4:F1000, "Saturday*", 'PBi LMS Data'!L4:L1000, "Zona Gol*")

 

[Peter_SSs]

Here is another approach that you could try.

=LET(r,'PBi LMS Data'!C4:P1000,t,INDEX(r,0,7),SUM(FILTER(INDEX(r,0,1),(t>=TIME(18,,))*(t<=TIME(18,59,))*(ISNUMBER(SEARCH("Monday",INDEX(r,0,4))))*(ISNUMBER(FIND(5,INDEX(r,0,14)))),0)))

 

[Tristram_ZX81]

That works. Thanks again, Etaf!

 

[etaf]

you are welcome , but Peter_SSs solution may be better and less resourceful