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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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

V
  • Question Question
SUM XLOOKUP
Replies
5
Views
239
StephenCrump
S
A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
452
amolvijay
A
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
848
Cubist
Cubist
M
  • Solved
How to count certain month from a column
Replies
3
Views
384
Joe4
Joe4
HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
290
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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