Counting Function Results

[Coaster750New]

I have a spreadsheet of many tens of thousands of rows and wish to prune the data to make it more manageable.
It is the output of a monitoring system that spits out a row of data every 15 minutes.
What I want to do is count how many rows of data there are at (say) a quarter past the hour, up to an end date (say, 1/1/23). I can use this in my macro then for various purposes.
I know how to use MINUTE() to get the minute value into a new column and then use COUNTIF on the new column but I don't want to do that as I will have to check through several macros to make sure they are addressing the correct columns.
Does anyone know how to count the occurrences of data at 15 minute intervals please?

 

[awoohaw]

Welcome to the forum!

You can use the CEILING function to determine your quarter hour groups.
Could you provide some sample data to use in your scenario? (xl2bb add in or a table paste, no images please).

Book2
	A	B	C
1	Time	Next Qtr Hr
2	1:29 AM	1:30 AM	=CEILING(A2,1/96)
3	1:15 AM	1:15 AM	=CEILING(A3,1/96)
4	1:17 AM	1:30 AM	=CEILING(A4,1/96)
5	1:11 AM	1:15 AM	=CEILING(A5,1/96)
6	1:12 AM	1:15 AM	=CEILING(A6,1/96)
7	1:10 AM	1:15 AM	=CEILING(A7,1/96)
8	1:29 AM	1:30 AM	=CEILING(A8,1/96)
9	1:13 AM	1:15 AM	=CEILING(A9,1/96)
10	1:18 AM	1:30 AM	=CEILING(A10,1/96)
11	1:18 AM	1:30 AM	=CEILING(A11,1/96)
12	1:16 AM	1:30 AM	=CEILING(A12,1/96)
13	1:03 AM	1:15 AM	=CEILING(A13,1/96)
14	1:02 AM	1:15 AM	=CEILING(A14,1/96)
15	1:14 AM	1:15 AM	=CEILING(A15,1/96)
16	1:14 AM	1:15 AM	=CEILING(A16,1/96)
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=CEILING(A2,1/96)
C2:C16	C2	=FORMULATEXT(B2)

 

[Coaster750New]

Thanks for your swift reply.

I'm not sure this will help much but the data is in the form of a left hand column of Date/Time and then 60 odd columns of sensor readings of which I have shown three below. There are no formulae and I don't want any.

I do not want to add any more columns. I just want a cell (e.g. A1) that shows the total number of cells at either 15 minutes, 30 minutes, 45 minutes or on the hour. The minutes value (e.g. 15) will be entered in cell A2 (say).

Maybe this is not possible but I had to ask on here.

Many thanks again.

15/07/2023 00:00	3.68	4.85	5.54
15/07/2023 00:15	3.68	4.85	5.49
15/07/2023 00:30	3.68	4.85	5.44
15/07/2023 00:45	3.67	4.85	5.35
15/07/2023 01:00	3.67	4.85	5.32
15/07/2023 01:15	3.66	4.85	5.28
15/07/2023 01:30	3.66	4.85	5.25
15/07/2023 01:45	3.66	4.85	5.20
15/07/2023 02:00	3.66	4.85	5.16
15/07/2023 02:15	3.65	4.85	5.06
15/07/2023 02:30	3.65	4.85	5.04
15/07/2023 02:45	3.65	4.85	5.02
15/07/2023 03:00	3.65	4.85	4.99
15/07/2023 03:15	3.65	4.85	4.96
15/07/2023 03:30	3.65	4.85	4.92
15/07/2023 03:45	3.65	4.85	4.89

 

[awoohaw]

I'm still not 100% sure of what you want.
But , take a look at this:

Book1
	A	B	C	D
1	6	>> Count
2	00:45:00	>> Quarter Hour
3
4	15/07/2023 00:00	3.68	4.85	5.54
5	15/07/2023 00:15	3.68	4.85	5.49
6	15/07/2023 00:30	3.68	4.85	5.44
7	15/07/2023 00:45	3.67	4.85	5.35
8	15/07/2023 01:00	3.67	4.85	5.32
9	15/07/2023 01:15	3.66	4.85	5.28
10	15/07/2023 01:30	3.66	4.85	5.25
11	15/07/2023 01:45	3.66	4.85	5.20
12	15/07/2023 02:00	3.66	4.85	5.16
13	15/07/2023 02:15	3.65	4.85	5.06
14	15/07/2023 02:30	3.65	4.85	5.04
15	15/07/2023 02:45	3.65	4.85	5.02
16	15/07/2023 03:00	3.65	4.85	4.99
17	15/07/2023 01:00	3.67	4.85	5.32
18	15/07/2023 01:15	3.66	4.85	5.28
19	15/07/2023 01:30	3.66	4.85	5.25
20	15/07/2023 01:45	3.66	4.85	5.20
21	15/07/2023 02:00	3.66	4.85	5.16
22	15/07/2023 02:15	3.65	4.85	5.06
23	15/07/2023 02:30	3.65	4.85	5.04
24	15/07/2023 02:45	3.65	4.85	5.02
25	15/07/2023 03:00	3.65	4.85	4.99
26	15/07/2023 03:15	3.65	4.85	4.96
27	15/07/2023 03:30	3.65	4.85	4.92
28	15/07/2023 03:45	3.65	4.85	4.89
Sheet1

Cell Formulas
Range	Formula
A1	A1	=SUM(--(ROUND(MINUTE(A4:A28),5)=ROUND(MINUTE(A2),5)))
A2	A2	=TIME(0,45,0)

 

[Coaster750New]

Thanks for your swift reply.

I'm not sure this will help much but the data is in the form of a left hand column of Date/Time and then 60 odd columns of sensor readings of which I have shown three below. There are no formulae and I don't want any.

I do not want to add any more columns. I just want a cell (e.g. A1) that shows the total number of cells at either 15 minutes, 30 minutes, 45 minutes or on the hour. The minutes value (e.g. 15) will be entered in cell A2 (say).

Maybe this is not possible but I had to ask on here.

Many thanks again.

15/07/2023 00:00	3.68	4.85	5.54
15/07/2023 00:15	3.68	4.85	5.49
15/07/2023 00:30	3.68	4.85	5.44
15/07/2023 00:45	3.67	4.85	5.35
15/07/2023 01:00	3.67	4.85	5.32
15/07/2023 01:15	3.66	4.85	5.28
15/07/2023 01:30	3.66	4.85	5.25
15/07/2023 01:45	3.66	4.85	5.20
15/07/2023 02:00	3.66	4.85	5.16
15/07/2023 02:15	3.65	4.85	5.06
15/07/2023 02:30	3.65	4.85	5.04
15/07/2023 02:45	3.65	4.85	5.02
15/07/2023 03:00	3.65	4.85	4.99
15/07/2023 03:15	3.65	4.85	4.96
15/07/2023 03:30	3.65	4.85	4.92
15/07/2023 03:45	3.65	4.85	4.89

I'm still not 100% sure of what you want.
But , take a look at this:

Book1
	A	B	C	D
1	6	>> Count
2	00:45:00	>> Quarter Hour
3
4	15/07/2023 00:00	3.68	4.85	5.54
5	15/07/2023 00:15	3.68	4.85	5.49
6	15/07/2023 00:30	3.68	4.85	5.44
7	15/07/2023 00:45	3.67	4.85	5.35
8	15/07/2023 01:00	3.67	4.85	5.32
9	15/07/2023 01:15	3.66	4.85	5.28
10	15/07/2023 01:30	3.66	4.85	5.25
11	15/07/2023 01:45	3.66	4.85	5.20
12	15/07/2023 02:00	3.66	4.85	5.16
13	15/07/2023 02:15	3.65	4.85	5.06
14	15/07/2023 02:30	3.65	4.85	5.04
15	15/07/2023 02:45	3.65	4.85	5.02
16	15/07/2023 03:00	3.65	4.85	4.99
17	15/07/2023 01:00	3.67	4.85	5.32
18	15/07/2023 01:15	3.66	4.85	5.28
19	15/07/2023 01:30	3.66	4.85	5.25
20	15/07/2023 01:45	3.66	4.85	5.20
21	15/07/2023 02:00	3.66	4.85	5.16
22	15/07/2023 02:15	3.65	4.85	5.06
23	15/07/2023 02:30	3.65	4.85	5.04
24	15/07/2023 02:45	3.65	4.85	5.02
25	15/07/2023 03:00	3.65	4.85	4.99
26	15/07/2023 03:15	3.65	4.85	4.96
27	15/07/2023 03:30	3.65	4.85	4.92
28	15/07/2023 03:45	3.65	4.85	4.89
Sheet1

Cell Formulas
Range	Formula
A1	A1	=SUM(--(ROUND(MINUTE(A4:A28),5)=ROUND(MINUTE(A2),5)))
A2	A2	=TIME(0,45,0)

That works perfectly thanks very much.

 

[awoohaw]

Great, happy to help.
Best Wishes.

 

[Coaster750New]

Great, happy to help.
Best Wishes.

Can I be cheeky please?
Is there a way of finding the first occurrence of(say) the 15 minute reading?
I've not used the way you showed me before and am not 100% sure how it works for anything other than "Sum" but it would be very handy to have a cell that shows where the first one is. Row number would be ideal.
Sorry for taking up your time.
Thanks again

 

[awoohaw]

Can I be cheeky please?
Is there a way of finding the first occurrence of(say) the 15 minute reading?
I've not used the way you showed me before and am not 100% sure how it works for anything other than "Sum" but it would be very handy to have a cell that shows where the first one is. Row number would be ideal.
Sorry for taking up your time.
Thanks again

Take a look at the last three columns for how the COUNT formula in cell A2 woarks.
Also there are formulas for the ROW nbr of worksheet as well as Record number in the lookup range:
(subsequent edit: After looking at the way I have it here the "ROUND" functions are not needed. But, to be most accurate because of floating decimal issues, the functions may need to be reversed.... so MINUTE(ROUND(..... )) instead of ROUND(MINUTE(...).)

Book1
	A	B	C	D	E	F	G	H
1	45	>>Desired Minute of any hour to check
2	6	>> Count				Logic of Counting Formula (Cell A2)
3	00:45	>> Quarter Hour				=SUM(--(ROUND(MINUTE(A7:A31),5)=ROUND(MINUTE(A3),5)))
4	10	>> Row Nbr of worksheet 1st Occ					sum trues/falses
5	4	>> Row In Matrix 1st Occurance					6
6						ROUND(MINUTE(A7:A31),5)	"="	ROUND(MINUTE(A3),5)
7	15/07/2023 00:00	3.68	4.85	5.54		0	FALSE	45
8	15/07/2023 00:15	3.68	4.85	5.49		15	FALSE	45
9	15/07/2023 00:30	3.68	4.85	5.44		30	FALSE	45
10	15/07/2023 00:45	3.67	4.85	5.35		45	TRUE	45
11	15/07/2023 01:00	3.67	4.85	5.32		0	FALSE	45
12	15/07/2023 01:15	3.66	4.85	5.28		15	FALSE	45
13	15/07/2023 01:30	3.66	4.85	5.25		30	FALSE	45
14	15/07/2023 01:45	3.66	4.85	5.20		45	TRUE	45
15	15/07/2023 02:00	3.66	4.85	5.16		0	FALSE	45
16	15/07/2023 02:15	3.65	4.85	5.06		15	FALSE	45
17	15/07/2023 02:30	3.65	4.85	5.04		30	FALSE	45
18	15/07/2023 02:45	3.65	4.85	5.02		45	TRUE	45
19	15/07/2023 03:00	3.65	4.85	4.99		0	FALSE	45
20	15/07/2023 01:00	3.67	4.85	5.32		0	FALSE	45
21	15/07/2023 01:15	3.66	4.85	5.28		15	FALSE	45
22	15/07/2023 01:30	3.66	4.85	5.25		30	FALSE	45
23	15/07/2023 01:45	3.66	4.85	5.20		45	TRUE	45
24	15/07/2023 02:00	3.66	4.85	5.16		0	FALSE	45
25	15/07/2023 02:15	3.65	4.85	5.06		15	FALSE	45
26	15/07/2023 02:30	3.65	4.85	5.04		30	FALSE	45
27	15/07/2023 02:45	3.65	4.85	5.02		45	TRUE	45
28	15/07/2023 03:00	3.65	4.85	4.99		0	FALSE	45
29	15/07/2023 03:15	3.65	4.85	4.96		15	FALSE	45
30	15/07/2023 03:30	3.65	4.85	4.92		30	FALSE	45
31	15/07/2023 03:45	3.65	4.85	4.89		45	TRUE	45
Sheet1

Cell Formulas
Range	Formula
A2	A2	=SUM(--(ROUND(MINUTE(A7:A31),5)=ROUND(MINUTE(A3),5)))
A3	A3	=TIME(0,A1,0)
A4	A4	=MATCH(ROUND(MINUTE(A3),5),ROUND(MINUTE(A7:A31),5),0)+ROW(A6)
A5	A5	=MATCH(ROUND(MINUTE(A3),5),ROUND(MINUTE(A7:A31),5),0)
G5	G5	=SUM(--(G7:G31))
F7:F31	F7	=ROUND(MINUTE(A7:A31),5)
G7:G31	G7	=F7=H7
H7:H31	H7	=ROUND(MINUTE($A$3),5)
Dynamic array formulas.

 

[Coaster750New]

Very many thanks again. All seems to work perfectly.
Apologies for the tardy reply - I got sick (literally).

 

[awoohaw]

Get well soon.