CODEWITHSUNDEEP
excelgoogle-sheets
Mark Bridgeman
Mark Bridgeman

Reputation: 13

Count num of values within range on every Nth cell

We have a data-set generated by an HR system of staff times, which isn't in the most helpful of formats.

We need to count the # of staff who start work before 10.00, and split those counts by week number (1-4) and day (Sun-Sat).

Every group of rows (1-4) is an individual.

Here's an example day's data: Week Start 1 - 2 10:00 3 09.00 4 09.55 1 09:25 2 10.00 3 10:25 4 10.25 1 09:25 2 09.55 3 08:00 4 10.00 1 10:25 2 09.25 3 09:25 4 10.15 1 10.00 2 08:25 3 09.45 4 11:25

So I can count the occurence of start times before 10am on the entire column with:

=COUNTIFS(L5:L203,">=00:30",L5:L203,"<=10:00")

but to do separate counts for every 4th row (so sum all for ones on week 1, all on week 2, etc) is beyond me.

I tried the following after searching similar questions, but it didn't work:

=COUNTIFS((MOD(ROW(L5:L203),5)=0),">=00:30",(MOD(ROW(L5:L203),5)=0),"<=10:00")

Ideally I'd be able to put values into a table with a count for week 1 on Sunday, week 2 on Sunday, week 1 on Monday, and so on.

Any ideas?

Upvotes: 0

Views: 660

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

To count the weeks use:

=COUNTIFs(B$2:B$21,"<=10:00",$A$2:$A$21,"=1")

=COUNTIFs(B$2:B$21,"<=10:00",$A$2:$A$21,"=2")

Changing =1, =2, =3, =4 for each weeks count. If column B is Monday and column C is Tuesday, you can drag the above formulas right across the row to get the weekly counts for other days.

Upvotes: 1

Related Questions