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

[Excel_Mike]

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.

 

[StephenCrump]

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?

 

[alansidman]

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.

 

[NickFru]

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 Date	End Date	Amount
A	01/01/2024	23/01/2024	£115.00
B	05/01/2024	25/01/2024	£210.00
C	07/01/2024	15/01/2024	£135.00
D	09/01/2024	10/02/2024	£66.00
E	07/01/2024	10/01/2024	£12.00
F	04/01/2024	05/02/2024	£165.00
G	04/01/2024	03/02/2024	£558.00
H	15/01/2024	25/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.

 

[Tetra201]

@ 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:

=SUMIFS($E3:$XFD3,$E$2:$XFD$2,">="&MAX($A3,$B3,B$17),$E$2:$XFD$2,"<="&MIN($C3,EOMONTH(B$17,0)))

 

[Sam_D_Ben]

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
	A	B	C	D	E	F
16	Output
17	Product	January	February	March	April	May
18	A	110	0	0	0	0
19	B	210	0	0	0	0
20	C	135	0	0	0	0
21	D	46	20	0	0	0
22	E	12	0	0	0	0
23	F	140	25	0	0	0
24	G	504	54	0	0	0
25	H	275	0	0	0	0
MultipleDate

Cell Formulas
Range	Formula
B18:F25	B18	=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)))

 

[Excel_Mike]

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.