Sumifs using name ranges

D

DavosCH32

New Member
Joined
Jun 27, 2014
Messages
11
I am trying to sum a column using multiple variables in named ranges. So far i have only been able to get the formula to work using one or a few of the criteria. I started with this but it's not working. "=SUM(SUMIFS(Amount,Dept,{"Boston","Atlanta"},PartNo,{"PYFO","PYRH"}))". Below is a portion of the spreadsheet. I would like to add more criteria such as a date range but everything i have tried has failed. Any help would be greatly appreciated.
[TABLE="width: 175"]
<colgroup><col style="width: 44pt; mso-width-source: userset; mso-width-alt: 2517;" width="59"> <col style="width: 44pt; mso-width-source: userset; mso-width-alt: 2474;" width="58"> <col style="width: 36pt; mso-width-source: userset; mso-width-alt: 2048;" width="48"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2901;" width="68"> <tbody>[TR]
[TD="class: xl69, width: 59, bgcolor: transparent"]A[/TD]
[TD="class: xl70, width: 58, bgcolor: transparent"] B [/TD]
[TD="class: xl68, width: 48, bgcolor: transparent"]C[/TD]
[TD="class: xl68, width: 68, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"]Date[/TD]
[TD="class: xl77, bgcolor: transparent"] Amount [/TD]
[TD="class: xl75, bgcolor: transparent"]Part #[/TD]
[TD="class: xl75, bgcolor: transparent"]Department[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: #92D050"]05/10/14[/TD]
[TD="class: xl85, bgcolor: #92D050"] $ 5.00 [/TD]
[TD="class: xl83, bgcolor: #92D050"]PYFO[/TD]
[TD="class: xl83, bgcolor: #92D050"]Atlanta[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: #92D050"]06/01/14[/TD]
[TD="class: xl85, bgcolor: #92D050"] $ 200.00 [/TD]
[TD="class: xl83, bgcolor: #92D050"]PYRH[/TD]
[TD="class: xl83, bgcolor: #92D050"]Atlanta[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: #92D050"]05/12/14[/TD]
[TD="class: xl85, bgcolor: #92D050"] $ 345.00 [/TD]
[TD="class: xl83, bgcolor: #92D050"]PYFO[/TD]
[TD="class: xl83, bgcolor: #92D050"]Boston[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: #92D050"]04/03/14[/TD]
[TD="class: xl85, bgcolor: #92D050"] $ 25.00 [/TD]
[TD="class: xl83, bgcolor: #92D050"]PYRH[/TD]
[TD="class: xl83, bgcolor: #92D050"]Boston[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: white"]05/22/14[/TD]
[TD="class: xl81, bgcolor: white"] $ 100.00 [/TD]
[TD="class: xl82, bgcolor: white"]PYCN[/TD]
[TD="class: xl74, bgcolor: transparent"]Chicago[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: white"]04/05/14[/TD]
[TD="class: xl81, bgcolor: white"] $ 215.00 [/TD]
[TD="class: xl82, bgcolor: white"]PYFO[/TD]
[TD="class: xl82, bgcolor: white"]Chicago[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: white"]06/17/14[/TD]
[TD="class: xl81, bgcolor: white"] $ 20.49 [/TD]
[TD="class: xl82, bgcolor: white"]FORV[/TD]
[TD="class: xl82, bgcolor: white"]Denver[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: white"]05/01/14[/TD]
[TD="class: xl81, bgcolor: white"] $ 75.00 [/TD]
[TD="class: xl82, bgcolor: white"]PYCN[/TD]
[TD="class: xl82, bgcolor: white"]Denver[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: white"]04/01/14[/TD]
[TD="class: xl81, bgcolor: white"] $ 10.00 [/TD]
[TD="class: xl82, bgcolor: white"]PYFO[/TD]
[TD="class: xl82, bgcolor: white"]Denver[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: white"]05/20/14[/TD]
[TD="class: xl81, bgcolor: white"] $ 5.00 [/TD]
[TD="class: xl82, bgcolor: white"]PYRH[/TD]
[TD="class: xl82, bgcolor: white"]Denver[/TD]
[/TR]
[TR]
[TD="class: xl78, bgcolor: transparent"]06/10/14[/TD]
[TD="class: xl79, bgcolor: transparent"] $ 10.00 [/TD]
[TD="class: xl74, bgcolor: transparent"]PYSB[/TD]
[TD="class: xl74, bgcolor: transparent"]Denver[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: white"]04/22/14[/TD]
[TD="class: xl81, bgcolor: white"] $ 290.00 [/TD]
[TD="class: xl82, bgcolor: white"]PYFO[/TD]
[TD="class: xl82, bgcolor: white"]Phoenix[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: white"]05/05/14[/TD]
[TD="class: xl81, bgcolor: white"] $ 55.82 [/TD]
[TD="class: xl82, bgcolor: white"]PYRH[/TD]
[TD="class: xl82, bgcolor: white"]Phoenix[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
you might want to check your named ranges because they do not seem to match your column headers

the semicolon in the second criteria is necessary in order to make the formula calculate correctly.

I don't think you would be able to add another criteria though with an array constant.

You may need to switch over to sumproduct

Excel 2010
ABCDEF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Date[/TD]
[TD="bgcolor: #FAFAFA"]Amount[/TD]
[TD="bgcolor: #FAFAFA"]Part No[/TD]
[TD="bgcolor: #FAFAFA"]Department[/TD]
[TD="align: right"][/TD]
[TD="align: right"]575[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #92D050, align: right"]5/10/2014[/TD]
[TD="bgcolor: #92D050, align: right"]$5.00 [/TD]
[TD="bgcolor: #92D050"]PYFO[/TD]
[TD="bgcolor: #92D050"]Atlanta[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #92D050, align: right"]6/1/2014[/TD]
[TD="bgcolor: #92D050, align: right"]$200.00 [/TD]
[TD="bgcolor: #92D050"]PYRH[/TD]
[TD="bgcolor: #92D050"]Atlanta[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #92D050, align: right"]5/12/2014[/TD]
[TD="bgcolor: #92D050, align: right"]$345.00 [/TD]
[TD="bgcolor: #92D050"]PYFO[/TD]
[TD="bgcolor: #92D050"]Boston[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #92D050, align: right"]4/3/2014[/TD]
[TD="bgcolor: #92D050, align: right"]$25.00 [/TD]
[TD="bgcolor: #92D050"]PYRH[/TD]
[TD="bgcolor: #92D050"]Boston[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5/22/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$100.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYCN[/TD]
[TD="bgcolor: #FAFAFA"]Chicago[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/5/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$215.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYFO[/TD]
[TD="bgcolor: #FFFFFF"]Chicago[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6/17/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$20.49 [/TD]
[TD="bgcolor: #FFFFFF"]FORV[/TD]
[TD="bgcolor: #FFFFFF"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5/1/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$75.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYCN[/TD]
[TD="bgcolor: #FFFFFF"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/1/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$10.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYFO[/TD]
[TD="bgcolor: #FFFFFF"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5/20/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$5.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYRH[/TD]
[TD="bgcolor: #FFFFFF"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6/10/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$10.00 [/TD]
[TD="bgcolor: #FAFAFA"]PYSB[/TD]
[TD="bgcolor: #FAFAFA"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/22/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$290.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYFO[/TD]
[TD="bgcolor: #FFFFFF"]Phoenix[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5/5/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$55.82 [/TD]
[TD="bgcolor: #FFFFFF"]PYRH[/TD]
[TD="bgcolor: #FFFFFF"]Phoenix[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet25​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]=SUM(SUMIFS(Amount,Department,{"Boston","Atlanta"},Part_No,{"PYFO";"PYRH"}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Amount[/TH]
[TD="align: left"]=Sheet25!$B$2:$B$14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Department[/TH]
[TD="align: left"]=Sheet25!$D$2:$D$14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Part_No[/TH]
[TD="align: left"]=Sheet25!$C$2:$C$14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Solution
ok, my mistake...I guess you could add a date criteria..

Excel 2010
ABCDEF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Date[/TD]
[TD="bgcolor: #FAFAFA"]Amount[/TD]
[TD="bgcolor: #FAFAFA"]Part No[/TD]
[TD="bgcolor: #FAFAFA"]Department[/TD]
[TD="align: right"][/TD]
[TD="align: right"]550[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #92D050, align: right"]5/10/2014[/TD]
[TD="bgcolor: #92D050, align: right"]$5.00 [/TD]
[TD="bgcolor: #92D050"]PYFO[/TD]
[TD="bgcolor: #92D050"]Atlanta[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #92D050, align: right"]6/1/2014[/TD]
[TD="bgcolor: #92D050, align: right"]$200.00 [/TD]
[TD="bgcolor: #92D050"]PYRH[/TD]
[TD="bgcolor: #92D050"]Atlanta[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #92D050, align: right"]5/12/2014[/TD]
[TD="bgcolor: #92D050, align: right"]$345.00 [/TD]
[TD="bgcolor: #92D050"]PYFO[/TD]
[TD="bgcolor: #92D050"]Boston[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #92D050, align: right"]4/3/2014[/TD]
[TD="bgcolor: #92D050, align: right"]$25.00 [/TD]
[TD="bgcolor: #92D050"]PYRH[/TD]
[TD="bgcolor: #92D050"]Boston[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5/22/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$100.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYCN[/TD]
[TD="bgcolor: #FAFAFA"]Chicago[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/5/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$215.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYFO[/TD]
[TD="bgcolor: #FFFFFF"]Chicago[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6/17/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$20.49 [/TD]
[TD="bgcolor: #FFFFFF"]FORV[/TD]
[TD="bgcolor: #FFFFFF"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5/1/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$75.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYCN[/TD]
[TD="bgcolor: #FFFFFF"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/1/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$10.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYFO[/TD]
[TD="bgcolor: #FFFFFF"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5/20/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$5.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYRH[/TD]
[TD="bgcolor: #FFFFFF"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6/10/2014[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$10.00 [/TD]
[TD="bgcolor: #FAFAFA"]PYSB[/TD]
[TD="bgcolor: #FAFAFA"]Denver[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/22/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$290.00 [/TD]
[TD="bgcolor: #FFFFFF"]PYFO[/TD]
[TD="bgcolor: #FFFFFF"]Phoenix[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5/5/2014[/TD]
[TD="bgcolor: #FFFFFF, align: right"]$55.82 [/TD]
[TD="bgcolor: #FFFFFF"]PYRH[/TD]
[TD="bgcolor: #FFFFFF"]Phoenix[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet25​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]=SUM(SUMIFS(Amount,Department,{"Boston","Atlanta"},Part_No,{"PYFO";"PYRH"},Date,">="&DATE(2014,5,1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Amount[/TH]
[TD="align: left"]=Sheet25!$B$2:$B$14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Date[/TH]
[TD="align: left"]=Sheet25!$A$2:$A$14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Department[/TH]
[TD="align: left"]=Sheet25!$D$2:$D$14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Part_No[/TH]
[TD="align: left"]=Sheet25!$C$2:$C$14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Weasel,
can you explain whats the difference between "," and ";" when using the constants above?
 
Upvote 0
well, i'm not sure about the particulars but generally the "," is feeding the criteria in columns, whereas the ";" is converting the criteria to rows to rows which the sumifs can handle.
from all my practice you can have 1 array constant separated with a comma and 1 criteria separated with a semicolon. Which I believe is the max amount of criteria you can set up this way that the sumifs can handle.

Aladin explained it much better a while back but I'm not able to locate the post.

Essentially the semicolon is transposing the critera for the sumif.

I'm sure there's a better explaination, if I could locate it I'll post it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

ExceLoki
  • Solved
Match name and return sum between date range
2
Replies
15
Views
842
ExceLoki
ExceLoki
L
  • Solved
Add Dynamic Sum with Dynamic Array when using SUMIFS
2
Replies
11
Views
953
LearnMeExcel
L
V
  • Solved
sum table based on rows and multiple years columns
Replies
2
Views
513
vampsthevampyre
V
A
  • Question Question
modified code peter_SSs (copy multiple columns across multiple sheets and calculate)
Replies
0
Views
587
abdo meghari
A
B
  • Solved
SUMIFS a product between dates
Replies
4
Views
555
bootdat
B

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

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