SUMIFS using a named range

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
I am trying to sum hours based on a list of earnings codes using a named range called Include. My formula is =sumif(a2:a6,Include,b2:b6). It is returning a 0. My Include range is =Sheet1!$G$1:$G$3. Any ideas why it's not adding up for me? (Once I am able to get this to add up, I will subtract the Do Not Include named range)
 

Attachments

  • Named Range in SUMIF.png
    Named Range in SUMIF.png
    62.5 KB · Views: 6

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi & welcome to MrExcel.
How about
Excel Formula:
=SUM(SUMIF(A2:A6,Include,B2:B6))
 
Upvote 0
Still gives me a 0 result.
 

Attachments

  • Named Range in SUMIF.png
    Named Range in SUMIF.png
    62.6 KB · Views: 5
Upvote 0
If you don't have the latest version, you'll need to array enter that with Ctrl+Shift+Enter
 
Upvote 0
I am using Excel 2010 so the sumif should work. I tried the Cntrl Shift Enter and get a 0 result. Image attached.
 

Attachments

  • Named Range in SUMIF.png
    Named Range in SUMIF.png
    62.6 KB · Views: 8
Upvote 0
You haven't included the SUM function.
 
Upvote 0
Solution
Just put the SUM function back in and did the array and it worked. Thank you both for your replies. Have a great day.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Some alternatives for your consideration

T202010b.xlsm
ABCD
1HoursAmountInclude
2Reg8Reg
3PTO4Reg1
4Train10
5Reg8
6Reg12
7
818
918
1018
1118
3a
Cell Formulas
RangeFormula
B8B8=SUMPRODUCT(Amount,--((Hours=D2)+(Hours=D3)))
B9B9=SUMPRODUCT(SUMIF(Hours,Include,Amount))
B10B10=SUMPRODUCT(--(ISNUMBER(MATCH(Hours,Include,0))),Amount)
B11B11=SUM(SUMIF(Hours,Include,Amount))
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Amount='3a'!$B$2:$B$6B8:B11
Hours='3a'!$A$2:$A$6B8:B11
Include='3a'!$D$2:$D$3B8:B11
 
Upvote 0
Some alternatives for your consideration

T202010b.xlsm
ABCD
1HoursAmountInclude
2Reg8Reg
3PTO4Reg1
4Train10
5Reg8
6Reg12
7
818
918
1018
1118
3a
Cell Formulas
RangeFormula
B8B8=SUMPRODUCT(Amount,--((Hours=D2)+(Hours=D3)))
B9B9=SUMPRODUCT(SUMIF(Hours,Include,Amount))
B10B10=SUMPRODUCT(--(ISNUMBER(MATCH(Hours,Include,0))),Amount)
B11B11=SUM(SUMIF(Hours,Include,Amount))
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Amount='3a'!$B$2:$B$6B8:B11
Hours='3a'!$A$2:$A$6B8:B11
Include='3a'!$D$2:$D$3B8:B11
Thank you for the information Dave. I understand the formulas except for the -- in B8 & B10; what do those mean? I tried them all out and they work great. The list I'm doing is a budget worksheet for work and I have to include all earning codes less certain ones in a particular "Do Not Include" list. There are also other criteria that I have to search for such as employee # and department # in order to sum the hours/dollars so I'm thinking Sumifs is the best route. Your example in B11 is probably the best one for this situation.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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