Sum adjacent cells based on criteria

[spoony40]

Hi, I need some help with a sum formula that adds a range based on a criteria.

The criteria is the cell value "Mar-18" which changes.

When its "Mar-18", it will sum the values for "Apple" for the month of "Jan-18", "Feb-18" and "Mar-18" which results in "9".

The "Next Qtr" value is the sum of the next 3 months ie. "Apr-18", "May-18" and "June-18" which results in "18".

Anyone knows how to do write these 2 formulas?

Thanks

Type	Mar-18	Next Qtr	Jan-18 <tbody> </tbody>	Feb-18 <tbody> </tbody>	Mar-18 <tbody> </tbody>	Apr-18 <tbody> </tbody>	May-18 <tbody> </tbody>	Jun-18 <tbody> </tbody>	Jul-18 <tbody> </tbody>	Aug-18 <tbody> </tbody>
Apple	9	18	2	3	4	5	6	7	8	9
Orange	6	27	1	3	2	7	9	11	13	15

<tbody>
</tbody>

Type	Apr-18	Next Qtr	Jan-18 <tbody> </tbody>	Feb-18 <tbody> </tbody>	Mar-18 <tbody> </tbody>	Apr-18 <tbody> </tbody>	May-18 <tbody> </tbody>	Jun-18 <tbody> </tbody>	Jul-18 <tbody> </tbody>	Aug-18 <tbody> </tbody>
Apple	12	21	2	3	4	5	6	7	8	9
Orange	12	38	1	3	2	7	9	11	13	15

<tbody>
</tbody>

 

[spoony40]

To make it abit clearer, i need the formula to calculate the sum of months jan/feb/mar, the "9" total, given the criteria "Mar-18". Also the formula to calculate the sum of apr/may/jun-18 given mar-18.

 

[jtakw]

Hi,

If I understand correctly, this will do what you want, although I think your Orange sum for Apr-18 is incorrect in your sample (instead of 38, should be 33):

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Type	Apr-18	Next Qtr	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18
2	Apple	12	21	2	3	4	5	6	7	8	9
3	Orange	12	33	1	3	2	7	9	11	13	15
Sheet7

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((MONTH(D$1:K$1)<=MONTH(B$1))*(MONTH(D$1:K$1)>MONTH(B$1)-3)*D2:K2)
C2	=SUMPRODUCT((MONTH(D$1:K$1)>MONTH(B$1))*(MONTH(D$1:K$1)<=MONTH(B$1)+3)*D2:K2)

Formulas copied down.

 

[spoony40]

Thanks, it works

 

[spoony40]

Actually, when the months cross over to the next year, it doesn't work. eg

Dec-18, jan-19, feb-19

 

[jtakw]

Thanks, it works

Actually, when the months cross over to the next year, it doesn't work. eg

Dec-18, jan-19, feb-19

You're welcome, I actually overcomplicated the formula that didn't account for crossing over the next year, use this updated formula, should work fine:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Type	Nov-18	Next Qtr	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19
2	Apple	12	21	2	3	4	5	6	7	8	9	3	4	5	6	7	8
3	Orange	15	24	1	3	2	7	9	11	13	15	4	5	6	7	8	9
Sheet7

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((D$1:Q$1<=B$1)*(D$1:Q$1>EOMONTH(B$1,-3))*D2:Q2)
C2	=SUMPRODUCT((D$1:Q$1>B$1)*(D$1:Q$1<=EOMONTH(B$1,3))*D2:Q2)

 

[spoony40]

ok thanks it works!

 

[jtakw]

You're welcome.