Formula to find the sum range to use in SUMIFS

[Kiriko]

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]

 

[baitmaster]

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

 

[Kiriko]

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!

 

[jtakw]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	Region	Account	18-Jan	18-Feb	18-Mar	18-Apr	18-May		Start Date	End Date
2	North	Total Sales	500	450	200	650	550		Period	4/1/2018	5/1/2018
3	South	Total Sales	425	382.5	170	552.5	467.5		Region	North
4	East	Total Sales	275	247.5	110	357.5	302.5		Account	Total Sales	1,200.00	1,200.00
5	West	Total Sales	525	472.5	210	682.5	577.5			Total COS	780.00	780.00
6
7	North	Total COS	325	292.5	130	422.5	357.5
8	South	Total COS	276.25	248.63	110.5	359.13	303.88
9	East	Total COS	178.75	160.88	71.5	232.38	196.63
10	West	Total COS	341.25	307.13	136.5	443.63	375.38
11
12	North	Total Credit Sales	250	225	100	325	275
13	South	Total Credit Sales	212.5	191.25	85	276.25	233.75
14	East	Total Credit Sales	137.5	123.75	55	178.75	151.25
15	West	Total Credit Sales	262.5	236.25	105	341.25	288.75
Sheet250

Cell Formulas
Range	Formula
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)

 

[Kiriko]

Thankyou, it is a lot simpler.

 

[jtakw]

You're welcome.

 

[Kiriko]

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?

 

[jtakw]

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.