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

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

• Welcome to Web Applications. You might use a pivot table, QUERY function among other approaches. If you need further help, please add some sample data and show what is the expected result.
– Rubén
Oct 4, 2021 at 16:29

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 