Dynamic Range names & sumifs error

N

nikegeo

Board Regular
Joined
Jul 23, 2010
Messages
52
I'm having a hard time getting the sumifs working with my dynamic range names ive created.

Here are the named ranges and formulas:
CLMarket- =OFFSET('Global Performance Highlights'!$F$44,0,0,COUNTA('Global Performance Highlights'!$F:$F)-1)
CLOffice- =OFFSET('Global Performance Highlights'!$G$44,0,0,COUNTA('Global Performance Highlights'!$G:$G)-1)
CLRevenue- =OFFSET('Global Performance Highlights'!$K$44,0,0,COUNTA('Global Performance Highlights'!$K:$K)-1)

I am referencing k44 because thats the table that hold my data and any additions i make to it id like the formula to automatically calculate.

My sumifs formula is as follows: =SUMIFS(CLRevenue,CLOffice,B2,CLMarket,A2)

So im trying to sum revenue if based on office and market

i keep getting a #value. Not sure what im doing wrong. Any help would be great.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Are there any #VAlUE! Errors within the CLRevenue Range?
Those errors will perpetuate themselves into the result of the SUMIFS...

If that's not the problem...
When troubleshooting formulas that use named ranges, I find this the best way to go is...

1. Does the formula work without the named ranges?
Does it do what you want if you hard code ranges?
=SUMIFS(K44:K1000,G44:G1000,B2,F44:F1000,A2)

2. If it works without the named ranges as in step 1..
Verify your named ranges refer to the range you think they do...
Go to the Name Box, and type in the name of the range, and press Enter.
Does the appropriate range get highlighted?


I suspect the named ranges don't refer to exactly the ranges you think they do...


Hope that helps.
 
Upvote 0
nikegeo said:
I'm having a hard time getting the sumifs working with my dynamic range names ive created.

Here are the named ranges and formulas:
CLMarket- =OFFSET('Global Performance Highlights'!$F$44,0,0,COUNTA('Global Performance Highlights'!$F:$F)-1)
CLOffice- =OFFSET('Global Performance Highlights'!$G$44,0,0,COUNTA('Global Performance Highlights'!$G:$G)-1)
CLRevenue- =OFFSET('Global Performance Highlights'!$K$44,0,0,COUNTA('Global Performance Highlights'!$K:$K)-1)

I am referencing k44 because thats the table that hold my data and any additions i make to it id like the formula to automatically calculate.

My sumifs formula is as follows: =SUMIFS(CLRevenue,CLOffice,B2,CLMarket,A2)

So im trying to sum revenue if based on office and market

i keep getting a #value. Not sure what im doing wrong. Any help would be great.
Click to expand...
Define Lrow as referring to:

=MATCH(9.99999999999999E+307,'Global Performance Highlights'!$K:$K)

CLMarket as referring to:

='Global Performance Highlights'!$F$44:INDEX('Global Performance Highlights'!$F:$F,Lrow)

CLOffice as referring to:

='Global Performance Highlights'!$G$44:INDEX('Global Performance Highlights'!$G:$G,Lrow)

and CLRevenue as referring to:

='Global Performance Highlights'!$K$44:INDEX(('Global Performance Highlights'!$K:$K,Lrow)

Now:

=SUMIFS(CLRevenue,CLOffice,B2,CLMarket,A2)

should succeed without being thwarted by ranges of unequal size.
 
Upvote 0
@JonMo1

the normal sumifs worked and calculated accurately

the name ranges look good

not sure if this can be causing the problem but the table size is D43:M83. row 43 = the header row

i want the dynamic name ranges because every month i will be adding new entries and would like them automatically calculated...

My calculations right now are in sheet1 with the dynamic ranges in 'Monthly Performance Highlights'
 
Upvote 0
If there is a blank cell in one of the ranges, they will be of different sizes. Try changing two of the definitions:

CLMarket- =OFFSET('Global Performance Highlights'!$F$44, 0, 0 ,COUNTA('Global Performance Highlights'!$F:$F)-1, 1)
CLOffice- =OFFSET('Global Performance Highlights'!$G$44, 0, 0, ROWS(CLMarket), 1)
CLRevenue- =OFFSET('Global Performance Highlights'!$K$44, 0, 0, ROWS(CLMarket), 1))
 
Upvote 0
mikerickson said:
If there is a blank cell in one of the ranges, they will be of different sizes. Try changing two of the definitions:

CLMarket- =OFFSET('Global Performance Highlights'!$F$44, 0, 0 ,COUNTA('Global Performance Highlights'!$F:$F)-1, 1)
CLOffice- =OFFSET('Global Performance Highlights'!$G$44, 0, 0, ROWS(CLMarket), 1)
CLRevenue- =OFFSET('Global Performance Highlights'!$K$44, 0, 0, ROWS(CLMarket), 1))
Click to expand...

Mikerickson - your solution worked. Yes the spreadsheet has numerous blank cells above the 43rd row.

In your solution why do you use the Rows function to create the dynamic range?
 
Last edited:
Upvote 0
nikegeo said:
@JonMo1

the normal sumifs worked and calculated accurately

the name ranges look good

not sure if this can be causing the problem but the table size is D43:M83. row 43 = the header row

i want the dynamic name ranges because every month i will be adding new entries and would like them automatically calculated...

My calculations right now are in sheet1 with the dynamic ranges in 'Monthly Performance Highlights'
Click to expand...

See also post #3...
 
Upvote 0
ROWS(CLMarket) returns the number of rows in the named range CLMarket.
By setting the row_count argument of the other OFFSET formulae to ROWS(CLMarket), that insures that all the ranges have the same number of rows.
Your mention of using the space before row 44 suggests that CLMarket (and the others) may not be referencing the correct range. The logic of SUMIFS will avoid this kind of error, but you might want to change this defintion.

CLMarket- =OFFSET('Global Performance Highlights'!$F$44, 0, 0 ,COUNTA('Global Performance Highlights'!$F:$F)-COUNTA('Global Performance Highlights'!$F$1:$F$43)-1, 1)
 
Upvote 0
Or use Aladin's solution in post #3

Or

=OFFSET('Global Performance Highlights'!$F$44, 0, 0 ,COUNTA('Global Performance Highlights'!$F$44:$F$65536))
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Creating Sub Totals for a Sort, Unique, Filter, Choose Cols dynamically
Replies
6
Views
321
ExcelToDAX
E
J
  • Question Question
Group last name based on range in alphabet
Replies
2
Views
171
Peter_SSs
Peter_SSs
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
194
Ramses505
R
S
  • Question Question
Dynamic Conditional Formatting for Range without a Macro
Replies
5
Views
367
Joe4
Joe4
M
  • Question Question
Dynamic Formula
Replies
2
Views
293
Mengli
M

Forum statistics

Threads
1,224,509
Messages
6,179,192
Members
452,893
Latest member
denay

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