Total Revenue according certain periods

[ricafonyat]

Dear friends,

Let me know if you can help me with this problem:

My objective is to calculate the total monthly revenue in cells H2:S2 (yellow) respecting the start and end dates.

Item	Price	Qty	Beginning Date	Ending Date			Jan - 2025	Feb - 2025	Mar - 2025	Apr - 2025	May - 2025	Jun - 2025	Jul - 2025	Aug - 2025	Sep - 2025	Oct - 2025	Nov - 2025	Dec - 2025
Banana	60,00	1	Jan - 2025	May - 2025		Total Revenue =>	60,00	100,00	60,00	60,00	60,00	18,00	43,00	43,00	25,00	35,00	35,00	0,00
Apple	40,00	1	Feb - 2025	Feb - 2025
Grape	18,00	1	Jun - 2025	Aug - 2025		Banana	60,00	60,00	60,00	60,00	60,00
Orange	25,00	1	Jul - 2025	Sep - 2025		Apple		40,00
pineapple	35,00	1	Oct - 2025	Nov - 2025		Grape						18,00	18,00	18,00
						Orange							25,00	25,00	25,00
						pineapple										35,00	35,00

Thanks in advance!

 

[Fluff]

How about

=SUMIFS($B:$B,$D:$D,"<="&EOMONTH(H1,-1)+1,$E:$E,">="&EOMONTH(H1,-1)+1)

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Item	Price	Qty	Beginning Date	Ending Date			Jan-2025	Feb-2025	Mar-2025	Apr-2025	May-2025	Jun-2025	Jul-2025	Aug-2025	Sep-2025	Oct-2025	Nov-2025	Dec-2025
2	Banana	60	1	Jan-2025	May-2025		Total Revenue =>	60	100	60	60	60	18	43	43	25	35	35	0
3	Apple	40	1	Feb-2025	Feb-2025
4	Grape	18	1	Jun-2025	Aug-2025		Banana	60	60	60	60	60
5	Orange	25	1	Jul-2025	Sep-2025		Apple		40
6	pineapple	35	1	Oct-2025	Nov-2025		Grape						18	18	18
7							Orange							25	25	25
8							pineapple										35	35
Sheet1

Cell Formulas
Range	Formula
H2:S2	H2	=SUMIFS($B$2:$B$6,$D$2:$D$6,"<="&H1,$E$2:$E$6,">"&EOMONTH(H1,-1))

 

[ricafonyat]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Item	Price	Qty	Beginning Date	Ending Date			Jan-2025	Feb-2025	Mar-2025	Apr-2025	May-2025	Jun-2025	Jul-2025	Aug-2025	Sep-2025	Oct-2025	Nov-2025	Dec-2025
2	Banana	60	1	Jan-2025	May-2025		Total Revenue =>	60	100	60	60	60	18	43	43	25	35	35	0
3	Apple	40	1	Feb-2025	Feb-2025
4	Grape	18	1	Jun-2025	Aug-2025		Banana	60	60	60	60	60
5	Orange	25	1	Jul-2025	Sep-2025		Apple		40
6	pineapple	35	1	Oct-2025	Nov-2025		Grape						18	18	18
7							Orange							25	25	25
8							pineapple										35	35
Sheet1

Cell Formulas
Range	Formula
H2:S2	H2	=SUMIFS($B$2:$B$6,$D$2:$D$6,"<="&H1,$E$2:$E$6,">"&EOMONTH(H1,-1))

It's almost 100% ok.
However, we have to consider the quantities entered in column C in the result.
If, for example, there are 2 bananas, the Total Revenue should be 120 and not 60.
In other words, it would be a sumproduct of two ranges.

 

[Phuoc]

Try change to:

=SUMPRODUCT($B$2:$B$6*$C$2:$C$6*($D$2:$D$6<=H1)*($E$2:$E$6>EOMONTH(H1,-1)))