How to count the number of times a certain label appears within a date range using Google Sheets?

Question

I often have this problem and haven't found a good solution. It drives me nuts!

I have a set of data with 1000s of rows and each has a date and some label -- might be salesperson, category, region, whatever. And there might be dozens of possible values for that label. I want to create a formula that counts how many times each label occurs in the dataset in rows within a certain date range.

I can use COUNTIF to get the number of records that fall within the date range but then I don't have them grouped by label. Or I can use COUNTIF with a dynamic list of the labels to get the number of rows that match each label but then I don't have a date filter. But when I try to combine them in COUNTIFS, I can't have a dynamic label that just grabs it from a field -- I have to manually enter each of the labels in a cell in the formula which doesn't work with a dataset this size, especially when the values are changing over time.

What's the best way to tackle this?

Kate

Answer 1

To judge which approach might be best for you would be a most difficult thing to do. OTOH with '000's of rows, you may find that some options are more efficient (less effect on recalculation) than others.

Please consider that there maybe/are many valid approaches. This answer covers only two options:

• COUNTIFS because you considered this a possibility
• QUERY because of its flexibility

---

COUNTIFS

In the example, the unique labels are listed in Column Z using =sort(unique(B6:B),1,0)

• no Date: =arrayformula(countifs(B6:B18,"="&Z6:Z11))
• Date = : =arrayformula(countifs(B6:B18,"="&Z6:Z11,A6:A18,"="&B1))
• Dates between: =arrayformula(countifs(B6:B18,"="&Z6:Z11,A6:A18,">="&B1,A6:A18,"<="&B2))

---

QUERY

• Basic query#1: list all and count labels by date

  • =query({A5:A,B5:B},"select Col1, Col2, Count(Col2) where Col1 is not null group by Col1, Col2")

• Basic query#2: listall and count the labels (data agnostic)

  • =query({A5:A,B5:B},"select Col2, Count(Col2) where Col1 is not null group by Col2 order by Col2 desc")

• Date equals: lists the labels and count for all records equal to a given date

  • =query({A5:A,B5:B},"select Col1, Col2, Count(Col2) where Col1 is not null and Col1=date '"&TEXT(B1,"yyyy-mm-dd")&"' group by Col1, Col2 order by Col2 desc",1)

• Date greater than: list the labels and count for all records after a given date

  • =query({A5:A,B5:B},"select Col1, Col2, Count(Col2) where Col1 is not null and Col1>date '"&TEXT(B1,"yyyy-mm-dd")&"' group by Col1, Col2",1)

• Date between#1: Lists dates labels and label counts for all records between two given dates

  • =query({A5:A,B5:B},"select Col1, Col2, Count(Col2) where Col1 is not null and Col1>=date '"&TEXT(B1,"yyyy-mm-dd")&"' and Col1<=date '"&TEXT(B2,"yyyy-mm-dd")&"' group by Col1, Col2 order by Col2 desc")

• Date between#2: lists labels and label counts for all records between two given dates

  • =query({A5:A,B5:B},"select Col2, Count(Col2) where Col1 is not null and Col1>=date '"&TEXT(B1,"yyyy-mm-dd")&"' and Col1<=date '"&TEXT(B2,"yyyy-mm-dd")&"' group by Col2 order by Col2 desc")

---

Sample Data