SUMIFS using a named range

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
5
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: 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
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=SUM(SUMIF(A2:A6,Include,B2:B6))
 

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Still gives me a 0 result.
 

Attachments

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you don't have the latest version, you'll need to array enter that with Ctrl+Shift+Enter
 

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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: 4

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You haven't included the SUM function.
 
Solution

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,670
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

rsboulevard

New Member
Joined
Oct 22, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top