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?