Counting partial time match

[Leeroy82]

Hi All,

I am trying to count the number of breaks that start on each 15 minute increment. I have tried wildcards as suggested on some other sites but it doesn't seem to be capturing the count.

Here is what I currently have but is returning a Zero result....HELP

=SUM(COUNTIFS($G$2:$G$1722,"*"&{":00",":15",":30",":45"}&"*"))

 

[Leeroy82]

There are intervals in the entire document that don't fall on a 15 minute increment also so I want to isolate those on the 15

 

[Phuoc]

Try this:

=SUMPRODUCT((MOD(MINUTE($G$2:$G$1722),15)=0)*($G$2:$G$1722>0))

 

[Toadstool]

Hi Leeroy82,

Does this do what you ask?

Leeroy82.xlsx
	G	H	I	J
1	Start Time	End Time		Result
2	10:30	10:45		2
3	12:45	13:00
4	14:00	14:15
5	15:25	15:30
6	16:30	16:45
7	17:45	18:00
8	19:00	19:15
9	19:25	19:44
10	12:45	13:00
11	14:00	14:15
Sheet1

Cell Formulas
Range	Formula
J2	J2	=SUMPRODUCT(--(MOD(MINUTE($G$2:$G$1722),15)<>0))

 

[Leeroy82]

Hi Leeroy82,

Does this do what you ask?

=SUMPRODUCT(--(MOD(MINUTE($G$2:$G$1722),15)<>0))

Thanks, whilst it doesn't it kind of does the reverse of what I was after so I can still work with that haha

 

[Toadstool]

Thanks, whilst it doesn't it kind of does the reverse of what I was after so I can still work with that haha

Then maybe
=SUMPRODUCT(--((MOD(MINUTE($G$2:$G$1722),15)=0)*($G$2:$G$1722<>"")))

 

[Toadstool]

...and if you want to highlight those not on 15 minute intervals then:

Leeroy82.xlsx
	G	H	I	J
1	Start Time	End Time		Result
2	10:00	10:45		8
3	12:45	13:00
4	14:00	14:15
5	15:25	15:30
6	16:30	16:45
7	17:45	18:00
8	19:00	19:15
9	19:25	19:44
10	12:45	13:00
11	14:00	14:15
12
13
Highlight

Cell Formulas
Range	Formula
J2	J2	=SUMPRODUCT(--((MOD(MINUTE($G$2:$G$1722),15)=0)*($G$2:$G$1722<>"")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G1722	Expression	=MOD(MINUTE($G2),15)<>0	text	NO

 

[Leeroy82]

Wor

Then maybe
=SUMPRODUCT(--((MOD(MINUTE($G$2:$G$1722),15)=0)*($G$2:$G$1722<>"")))

Worked perfect, thanks very much for your help!