Help with using name ranges and sum function please!

soteman2005

New Member
Joined
Nov 24, 2005
Messages
32
Hi,

I am creating a model in excel using range names for every row which works really well. The model is run across several years which are allocated a column each. I need to sum some cells which are all in 1 year e.g. =sum(b1:b5) but when i use the range names for row 1:row 5, excel sums the entire named range from A1:IX1 to A5:IX5, instead of just the cells in the active column.

Does anyone know a way to do this? I'm thinking that there might be an intersect function or something which will allow me to sum the intersecting cells of the named column range and named row ranges?

Similarly, I would also like to be able to reference a cell from the previous year to do an average, so is there a way to do that using the range name i.e I am calculating a 2006 average but I need the 2005 value for susbcribers, but the name range gives me the 2006 value.

Many thanks in advance

Adam
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Welcome to MrExcel - It may well be that named ranges are not the way to go here.

This sounds like a job for INDEX(MATCH(),MATCH()) -- can you describe a bit more about your data and criteria for summing/counting?
 
Upvote 0
Hi Jon,

I am working across years 2003 to 2013. There are quite a few instances in which I want to sum data, but here is one example;

2003 2004 2005 etc.
Cost A £12 £13 £15
Cost B £10 £11 £5
etc.
Total £22 £24 £20

but obviously I have more rows to sum and more years. I have range named each row as I have about 450 rows in each worksheet and I often need to add extra rows.

so for total I would use a formula like =sum(costA:costB) using range names, but that sums CostA for 2003, 2004, 2005 and Cost B for 2003, 2004, 2005 which I obviously don't want.

The issue with referencing previous years might for example be doing an average cost for 2003-2004 so I would want to be able to reference CostA 2003 whilst in the 2004 column and I have no idea how to do this with range names.

Any help would be greatly appreciated.
 
Upvote 0
Book2
ABCDEFGH
12003200420052006Cost A2004
2Cost A121315Cost B2005
3Cost A111069
4Cost B1812
5Cost B7
6Cost C55667788
7Cost C121232
8
Sheet1


The above assumes column A is sorted ascending.
 
Upvote 0
I'm thinking that there might be an intersect function or something which will allow me to sum the intersecting cells of the named column range and named row ranges?

Yes, look at my formulae:
Complex Cond Format.xls
ABCDE
1NameYear 2003Year 2004Year 2005
2CostA1142335
3CostB233245
4CostC987
5CostD141518
6CostE454542
7CostF666769
8
991100112
10
Sheet2
 
Upvote 0

Forum statistics

Threads
1,226,390
Messages
6,190,738
Members
453,616
Latest member
nathancook

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