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*")

 

[etaf]

put * each side of the name

#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*")

Book7
	A	B	C	D
1	zona gol			3
2	2022 Zona Gol May
3	Zona gol - 1
4	Zona
5	Gol
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTIF(A1:A8,"*Zona Gol*")

 

[Tristram_ZX81]

That's perfect. Thank Etaf.

 

[etaf]

you are welcome

 

[Tristram_ZX81]

I've hit another snag with this one....

The below formula finds any row with 5 written in the P column that kicks off between 18:00 and 18:59 in the I column and repeats the corresponding number in the C column.

=SUMIFS('PBi LMS Data'!C4:C1000, 'PBi LMS Data'!I4:I1000, ">=18:00:00", 'PBi LMS Data'!I4:I1000, "<=18:59:00", 'PBi LMS Data'!F4:F1000, "Monday*", 'PBi LMS Data'!P4:P1000, "*5*")

But it's weirdly working if 5 isnt the only character in the P column. For example, it will pick up hdhdgdh5 but not just 5.

Any ideas?

 

[etaf]

thats because of the *
which are wild cards

So anything before a 5 is *5 and anything after a 5 is 5*
so does both
*5*

and will pickup thing like
hdhd5gdh
hdhdgdh5
hdhdgd5h
hd5hdgdh

 

[Tristram_ZX81]

Thanks Etaf. So how do I get to recognise either situation, either a cell showing 5 or xhxtxh5ts?

 

[etaf]

if you want just a 5
then
5
if you want 5 at the end
*5
5 only at the beginning
5*
or 5 anywhere
*5*

 

[Tristram_ZX81]

I want 5 and 5 anywhere. I want it to recognise all of the below, but as it stands it only recognies row A, C, D and E.

 

[etaf]

when you put "" around you change numbers to text
hence why B does not work
Not sure but maybe

=SUM(SUMIFS('PBi LMS Data'!C4:C1000, 'PBi LMS Data'!I4:I1000, ">=18:00:00", 'PBi LMS Data'!I4:I1000, "<=18:59:00", 'PBi LMS Data'!F4:F1000, "Monday*", 'PBi LMS Data'!P4:P1000, {5,"*5*"}))