How to perform specific counting in Google Sheets?

Other Questions Google Sheets
1

Hey guys, I’m stuck on a Google Sheets problem. I’ve got this sheet with subjects and people’s names for different days and times. I want to count stuff in a special way, but I can’t figure it out.

Here’s what I’m trying to do:

  1. Count how many people are listed for each subject, day, and time (day/night).
  2. Calculate the total duration for each subject per day.
  3. Make it all dynamic so adding or removing rows doesn’t mess things up.

I’ve tried using COUNTA and INDIRECT, but I’m not getting the results I want. The tricky part is I need it to find the subjects no matter where they are in the sheet.

Here’s a simplified version of what I’m aiming for:

| Day       | People (Day) | People (Night) | Total Duration |
|-----------|--------------|----------------|----------------|
| Monday    | 2            | 1              | 120            |
| Tuesday   | 1            | 3              | 180            |
| Wednesday | 3            | 1              | 360            |

Any ideas on how to make this work? I’m totally stuck!

2

I’ve tackled a similar challenge before, and I found ARRAYFORMULA combined with COUNTIFS to be incredibly effective. Here’s what worked for me:

=ARRAYFORMULA(
COUNTIFS(
YourSubjectColumn, UniqueSubjects,
YourDayColumn, UniqueDays,
YourTimeColumn, “Day”
)
)

This formula dynamically adapts as you add or remove rows. For the duration calculation, you could use SUMIFS in a similar way:

=ARRAYFORMULA(
SUMIFS(
YourDurationColumn,
YourSubjectColumn, UniqueSubjects,
YourDayColumn, UniqueDays
)
)

Remember to replace the column references with your actual data ranges. It took some trial and error, but once set up, it’s been a real time-saver for me. Hope this helps!

3

have u tried using QUERY function? it’s pretty powerful for this kinda stuff. you could do something like:

=QUERY(YourDataRange, “SELECT Day, COUNT(Name) WHERE Time=‘Day’ GROUP BY Day”)

for the day count. do similar for night and duration. might need to tweak based on ur exact setup tho

4

I’ve found that using FILTER in combination with UNIQUE can be quite effective for this type of problem. Here’s an approach that might work:

=ARRAYFORMULA(
COUNTIFS(
FILTER(YourDataRange, YourDataRange[,SubjectColumn] = UNIQUE(YourDataRange[,SubjectColumn])),
UNIQUE(YourDataRange[,DayColumn]),
FILTER(YourDataRange, YourDataRange[,TimeColumn] = “Day”)
)
)

This formula dynamically adjusts as you add or remove data. For the duration, you could use:

=ARRAYFORMULA(
SUMIFS(
FILTER(YourDataRange[,DurationColumn], YourDataRange[,SubjectColumn] = UNIQUE(YourDataRange[,SubjectColumn])),
FILTER(YourDataRange[,DayColumn], YourDataRange[,SubjectColumn] = UNIQUE(YourDataRange[,SubjectColumn])),
UNIQUE(YourDataRange[,DayColumn])
)
)

These formulas might need some tweaking based on your specific setup, but they should provide a solid starting point.