0

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?

Simplified example

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.

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

enter image description here

3
  • 2
    Good start, but if I understand the question, the example is "during January". It seems like the criteria would be startdate>=January 1, enddate < February 1 (which could be derived using DATE and month+1 for the enddate). In this formula, only January 1 qualifies. – fixer1234 May 30 '19 at 20:58
  • I'm getting close on this. Since the days are irrelevant we can set them all to day one. The following formula allows any date to be set as mm/01/yyyy: =EOMONTH(cell,-1)+1 I ran with this and then used the countifs function, eliminating duplicate dates, but now numbers don't look right. – MrDiou May 30 '19 at 23:12
  • I've updated the data and the prompt to reflect what's been found. I'm not sure we've got it just yet though... – MrDiou May 31 '19 at 0:31

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.