Formula to find the sum range to use in SUMIFS

Kiriko

New Member
Joined
May 15, 2018
Messages
16
Hi, I wonder if it's possible to create an excel formula that can specify a sum range to be used in sumifs fx if there were 3 criteria used just to show what row to look at.

Basically I need to specify to search in column find all the cell rows that have a certain value and then within that result check the next column to find the row that has a certain value then in that row add the cells based on a specified column header. The last bit is where the sumifs come in, summing based on date.

I can get the same info using pivot but my data changes every month and I want to be able to just refresh the summary rather than copy/pasting values from the pivot table since i'll be doing this for at least 100 worksheets therefor 100 pivot tables.

Below is an example I hope it makes things clearer:


[TABLE="width: 421"]
<colgroup><col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="width: 121, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Region[/TD]
[TD="width: 72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Account[/TD]
[TD="width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Jan-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Feb-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Mar-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Apr-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]May-18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 500.00 [/TD]
[TD="bgcolor: transparent"] 450.00 [/TD]
[TD="bgcolor: transparent"] 200.00 [/TD]
[TD="bgcolor: transparent"] 650.00 [/TD]
[TD="bgcolor: transparent"] 550.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 425.00 [/TD]
[TD="bgcolor: transparent"] 382.50 [/TD]
[TD="bgcolor: transparent"] 170.00 [/TD]
[TD="bgcolor: transparent"] 552.50 [/TD]
[TD="bgcolor: transparent"] 467.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 275.00 [/TD]
[TD="bgcolor: transparent"] 247.50 [/TD]
[TD="bgcolor: transparent"] 110.00 [/TD]
[TD="bgcolor: transparent"] 357.50 [/TD]
[TD="bgcolor: transparent"] 302.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 525.00 [/TD]
[TD="bgcolor: transparent"] 472.50 [/TD]
[TD="bgcolor: transparent"] 210.00 [/TD]
[TD="bgcolor: transparent"] 682.50 [/TD]
[TD="bgcolor: transparent"] 577.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 325.00 [/TD]
[TD="bgcolor: transparent"] 292.50 [/TD]
[TD="bgcolor: transparent"] 130.00 [/TD]
[TD="bgcolor: transparent"] 422.50 [/TD]
[TD="bgcolor: transparent"] 357.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 276.25 [/TD]
[TD="bgcolor: transparent"] 248.63 [/TD]
[TD="bgcolor: transparent"] 110.50 [/TD]
[TD="bgcolor: transparent"] 359.13 [/TD]
[TD="bgcolor: transparent"] 303.88 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 178.75 [/TD]
[TD="bgcolor: transparent"] 160.88 [/TD]
[TD="bgcolor: transparent"] 71.50 [/TD]
[TD="bgcolor: transparent"] 232.38 [/TD]
[TD="bgcolor: transparent"] 196.63 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 341.25 [/TD]
[TD="bgcolor: transparent"] 307.13 [/TD]
[TD="bgcolor: transparent"] 136.50 [/TD]
[TD="bgcolor: transparent"] 443.63 [/TD]
[TD="bgcolor: transparent"] 375.38 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 250.00 [/TD]
[TD="bgcolor: transparent"] 225.00 [/TD]
[TD="bgcolor: transparent"] 100.00 [/TD]
[TD="bgcolor: transparent"] 325.00 [/TD]
[TD="bgcolor: transparent"] 275.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 212.50 [/TD]
[TD="bgcolor: transparent"] 191.25 [/TD]
[TD="bgcolor: transparent"] 85.00 [/TD]
[TD="bgcolor: transparent"] 276.25 [/TD]
[TD="bgcolor: transparent"] 233.75 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 137.50 [/TD]
[TD="bgcolor: transparent"] 123.75 [/TD]
[TD="bgcolor: transparent"] 55.00 [/TD]
[TD="bgcolor: transparent"] 178.75 [/TD]
[TD="bgcolor: transparent"] 151.25 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 262.50 [/TD]
[TD="bgcolor: transparent"] 236.25 [/TD]
[TD="bgcolor: transparent"] 105.00 [/TD]
[TD="bgcolor: transparent"] 341.25 [/TD]
[TD="bgcolor: transparent"] 288.75 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Extract[/TD]
[TD="bgcolor: transparent, colspan: 5"]Total sales and COS for North for the months April and May[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Start Date[/TD]
[TD="bgcolor: transparent"]End Date[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Period[/TD]
[TD="bgcolor: transparent, align: right"]1/04/2018[/TD]
[TD="bgcolor: transparent, align: right"]1/05/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Region[/TD]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Account[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 1,200.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 780.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
I pasted your date in range(A1:G15)

Other data inputs I've used:
April 18 is in cell C20
May 18 in cell C21
North in cell C23
Total COS in cell C24

I identify the column numbers for start and end:
in D20: =MATCH(C20,$A$1:$G$1,0) = column 6 i.e. April
in D21: =MATCH(C21,$A$1:$G$1,0) = column 7 i.e. May

I identify the row number for North/Total COS using an array formula (entered using Shift + Ctrl + Enter):
in D25: =MATCH(C23&C24,A1:A15&B1:B15,0)

I then answer the Total COS question with the formula:
=SUM(OFFSET(A1,D25-1,D20-1,1,D21-D20+1))

Hope this helps
 
Last edited:
Upvote 0
Thanks Baitmaster. The Arrayed formula for MATCH was basically what I was after. On the actual data I had to tweak it a bit so I used SUMIFS(INDEX(A1:G15,MATCH(C24&C25,A1:A15&B1:B15,0),0),A1:G1,C20,A1:15,C2). I keep getting 0 in the example data but it works fine on the actual data.

Now I know about arrayed match formulas which is really useful.

Thanks again for you help!
 
Upvote 0
Hi,

You can also just use a SUMPRODUCT formula.

If the Top Row (Jan-18, Feb-18, Mar-18) is an Actual date that starts on the First of each month, use L4 formula; otherwise, use K4 formula to test for Month.

Either formula copied down from K4 to K5:


Book1
ABCDEFGHIJKL
1RegionAccount18-Jan18-Feb18-Mar18-Apr18-MayStart DateEnd Date
2NorthTotal Sales500450200650550Period4/1/20185/1/2018
3SouthTotal Sales425382.5170552.5467.5RegionNorth
4EastTotal Sales275247.5110357.5302.5AccountTotal Sales1,200.001,200.00
5WestTotal Sales525472.5210682.5577.5Total COS780.00780.00
6
7NorthTotal COS325292.5130422.5357.5
8SouthTotal COS276.25248.63110.5359.13303.88
9EastTotal COS178.75160.8871.5232.38196.63
10WestTotal COS341.25307.13136.5443.63375.38
11
12NorthTotal Credit Sales250225100325275
13SouthTotal Credit Sales212.5191.2585276.25233.75
14EastTotal Credit Sales137.5123.7555178.75151.25
15WestTotal Credit Sales262.5236.25105341.25288.75
Sheet250
Cell Formulas
RangeFormula
K4=SUMPRODUCT((MONTH(C$1:G$1)>=MONTH(J$2))*(MONTH(C$1:G$1)<=MONTH(K$2))*(A$2:A$15=J$3)*(B$2:B$15=J4)*C$2:G$15)
L4=SUMPRODUCT((C$1:G$1>=J$2)*(C$1:G$1<=K$2)*(A$2:A$15=J$3)*(B$2:B$15=J4)*C$2:G$15)
 
Upvote 0
Hey jtakw, if there was another column between B and C which had additional information (such as region code or something like that - information only) would I still be able to use the SUMPRODUCT as it is written in your answer?
 
Upvote 0
That depends on whether the additional information/column is part of the Conditions for the SUM...

If it's Not, then just adjust the cell/range references of the formula to the new set up.

If it Is a new/additional SUM condition, then I'll need to know what that information is and what the new criteria is to adjust the formula to suit.
 
Upvote 0

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

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