# SUMIFS using a named range

#### rsboulevard

##### New Member
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
62.5 KB · Views: 3

### 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.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=SUM(SUMIF(A2:A6,Include,B2:B6))``

#### rsboulevard

##### New Member
Still gives me a 0 result.

#### Attachments

• Named Range in SUMIF.png
62.6 KB · Views: 3

#### RoryA

##### MrExcel MVP, Moderator
If you don't have the latest version, you'll need to array enter that with Ctrl+Shift+Enter

#### rsboulevard

##### New Member

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
62.6 KB · Views: 4

#### Fluff

##### MrExcel MVP, Moderator
You haven't included the SUM function.

#### rsboulevard

##### New Member

Just put the SUM function back in and did the array and it worked. Thank you both for your replies. Have a great day.

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

#### Dave Patton

##### Well-known Member

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

#### rsboulevard

##### New Member

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.

Replies
3
Views
135
Replies
2
Views
126
Replies
0
Views
65
Replies
2
Views
108
Replies
5
Views
154

1,130,172
Messages
5,640,584
Members
417,152
Latest member
DayTimeSeby

### 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.

### Which adblocker are you using?

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

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