How to SUM monthly sales by product with multiple date criteria?

Excel_Mike

New Member
Joined
Feb 14, 2025
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
I'm trying to track product sales and am not sure the best approach to take for the output table formula.

I want to display the sales per product (E3:E10) with a column for each month, only counting sales between the dates starting with the later of Start or Alternate Start (Column A or B) and ending with End Date (Column C). Daily sales per product fluctuate, many of the time periods are partial months, and I'd like to be able to continue adding new month's data & output column in an ongoing basis.

I was planning to use something like SUMIFS(XLOOKUP()) but I'm not finding an elegant way to apply the logic needed with multiple criteria like this.

Screenshot 2025-02-14 at 10.41.14 AM.png
 
Welcome to the Forum!

If I'm reading this correctly, you want to include sales:

- For Product A, from the dates 1 January to 22 January 2024 inclusive
- For Product B, from the dates 5 January to 25 January 2024 inclusive
etc.

So the output table will show nil for the months February to May, regardless of what sales data exists?

Or do you intend expanding the Start/Alternate Start/End table? And if so, what will it look like?
 
Upvote 0
In addition to Stephen's questions, I would urge you to present your data using XL2BB. We cannot manipulate data in a picture and do you really expect us to retype all your data so that we can determine if our solutions work? Help us to help you by making it easy for us.
 
Upvote 0
Just had a look at this and have laid this out in a slightly different format than your data above and have specified the date ranges rather than the months so you may want to give this a try so here goes;

Output
Start Date/Alternative Start DateEnd DateAmount
A01/01/202423/01/2024£115.00
B05/01/202425/01/2024£210.00
C07/01/202415/01/2024£135.00
D09/01/202410/02/2024£66.00
E07/01/202410/01/2024£12.00
F04/01/202405/02/2024£165.00
G04/01/202403/02/2024£558.00
H15/01/202425/01/2024£275.00


In the first date 01/01/2024, I have created a simple IF based on the cells A3 & B3 in your data, =IF(A3>B3,A3,B3). In the column Start Date/Alternative Start Date, this will give the later date between the two date ranges in your original data. So essentially, instead of using Jan, Feb, Mar, Apr & May, I have just used the above instead by creating a separate table, similar to what you have done from cells A16 To F25 on your data.

Then to calculate, the sales between the chosen dates, enter the following formula using LET & SUMPRODUCT;
=LET(sumrange, $F$3:$Y$10,product, $E$3:$E$10,daterange,$F$2:$Y$2, SUMPRODUCT(--(product=E3)*(sumrange)*(daterange>=B18)*(daterange<=C18)))

If you need to change the start date or alternate date for whatever reason in your original date, this will automatically update the formula and provide updated sales results depending on what is the later date.

If you haven't use the LET function previously, you are just assigning a name followed by the data range, its used primarily to prevent you from having to stipulate the range of cells over and over again.

Hope this helps.
 
Upvote 0
@ Excel_Mike:
With your original data setup, and assuming entries in row 17 are the 1st of the month formatted as mmmm, try placing the following formula in cell B18 and drag-copying it down and to the right as needed:
Excel Formula:
=SUMIFS($E3:$XFD3,$E$2:$XFD$2,">="&MAX($A3,$B3,B$17),$E$2:$XFD$2,"<="&MIN($C3,EOMONTH(B$17,0)))
 
Upvote 1
Solution
Another approach,
Assuming Cell range (NG2) is the last day of the year, Dated 12/31/2024 with the same values extending to Range(F3:NG10)
Book1
ABCDEF
16Output
17ProductJanuaryFebruaryMarchAprilMay
18A1100000
19B2100000
20C1350000
21D4620000
22E120000
23F14025000
24G50454000
25H2750000
MultipleDate
Cell Formulas
RangeFormula
B18:F25B18=SUMPRODUCT((TEXT($F$2:$NG$2,"mmmm")=B$17) * ($F$2:$NG$2>=MAX($A3,$B3)) * ($F$2:$NG$2<=MIN($C3,EOMONTH(DATEVALUE("1/"&B$17&"/2024"),0))) * ($F3:$NG3) * ISNUMBER(MATCH($E$3:$E$10,$A18,0)))
 
Upvote 1
Thank you all for the input! Apologies for not submitting my data as XL2BB, newbie mistake! Sam, Nick, and Tetra solutions all work, I marked Tetra's as the solution since it was the closest to what I'm working for. Appreciate the quick help, you all saved me a ton of frustration.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top