0

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

1
  • 1
    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 at 16:29
0

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))

countifs


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")

query


Sample Data

data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.