Sum adjacent cells based on criteria

S

spoony40

New Member
Joined
Feb 27, 2015
Messages
17
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


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Mar-18[/TD]
[TD] Next Qtr[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Jan-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Feb-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Mar-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Apr-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]May-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Jun-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Jul-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Aug-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]15[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Apr-18[/TD]
[TD] Next Qtr[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Jan-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Feb-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Mar-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Apr-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]May-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Jun-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Jul-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Aug-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]15

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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.
 
Upvote 0
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
ABCDEFGHIJK
1TypeApr-18Next QtrJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18
2Apple122123456789
3Orange123313279111315
Sheet7
Cell Formulas
RangeFormula
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.
 
Upvote 0
spoony40 said:
Thanks, it works
Click to expand...

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

Dec-18, jan-19, feb-19
Click to expand...

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
ABCDEFGHIJKLMNOPQ
1TypeNov-18Next QtrJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19
2Apple122123456789345678
3Orange152413279111315456789
Sheet7
Cell Formulas
RangeFormula
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
79
AhoyNC
A
V
  • Question Question
SUM XLOOKUP
Replies
5
Views
265
StephenCrump
S
A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
486
amolvijay
A
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
999
Cubist
Cubist
P
  • Solved
Vlookup and match issue
Replies
7
Views
91
etaf
etaf

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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