How can I count the number of cells that fall within a date range?

Question

I'm trying to look at hundreds of products being sold over the last few years to compare sales performance. i.e. did Product A sell more quickly than Product X or were there just fewer products to choose from at that time?

Using the examples below, how many products were being sold during January 2019?

More complex data

I've tried using countifs to figure it out but I don't think that's the right approach (at least, I can't wrap my mind around it if it is.)

It probably goes without saying that the date needs to be segmented by month and not the specific date. In other words, a table with each start date according to the day is far too specific.

Ideally, I would like a chart or table showing the number of products being sold during each month and then be able to easily view which products were being sold.

EDIT I feel like I'm really close on this but the values aren't totally right. I've updated the more complex data using both the following formulas guided by the comments:

=COUNTIFS(start_date,"<="&M2,end_date,">="&M2)

To count the incidences of the specified date with a range and then

=EOMONTH(B2,-1)+1

to simplify the data so all dates start on mm/01/yyyy.

This appears to work on a small scale but the numbers appear off with I apply it to my actual data. For instance, there are some dates that show 80 products being sold within that range but that's way higher than what we've ever offered.

I'd love any help checking this work! Thanks for all the contributions so far.

Answer 1

I think I have it.

First, I added a "Timeframe" cell which has 1/1/2019 in it. I also created a named range start_date and end_date. Then you can use a COUNTIFS():

=COUNTIFS(start_date,"<="&B30,end_date,">="&B30)

Where B30 is 01/01/2019.

In the screenshot below, Column D is a similar formula, =COUNTIFS(B2,"<="&$B$30,C2,">="&$B$30), and summed at the end, which matches the COUNTIFS() result.

You may actually prefer this one, COUNTIFS() per row, as you can then easily filter out the 0 to show only 1, which means that product was sold. (Or use a pivot table, etc).