summing multiple cells from another WS

[steveh]

2003 sp3

Afternoon all

Is there a smarter was to sum cells from another worksheet rather than typing out something like this

=Planner!AR7+Planner!AR72+Planner!AR137+Planner!AR201+Planner!AR266+Planner!AR331+Planner!AR396+Planner!AR461+Planner!AR526+Planner!AR591+Planner!AR666+Planner!AR721

I have tried to Google for 'Sum several cells from another worksheet' etc. but have not happened upon a solution

[steveh]

2003 sp3

Afternoon all

Is there a smarter was to sum cells from another worksheet rather than typing out something like this

=Planner!AR7+Planner!AR72+Planner!AR137+Planner!AR201+Planner!AR266+Planner!AR331+Planner!AR396+Planner!AR461+Planner!AR526+Planner!AR591+Planner!AR666+Planner!AR721

I have tried to Google for 'Sum several cells from another worksheet' etc. but have not happened upon a solution

I should have played around a little longer, it was as simple as (me?)

=SUM(Planner!AR7+AR72+AR137+AR201+AR266+AR331+AR396+AR461+AR526+AR591+AR666+AR721)

[HansV]

No! Your formula will sum AR7 from the Planner sheet and the other cells from the sheet containing the formula!

You could select the 12 cells and name them by typing a name in the address box on the left hand side of the formula bar. You can then use the name in the formula, e.g. =SUM(MyCells).

[steveh]

No! Your formula will sum AR7 from the Planner sheet and the other cells from the sheet containing the formula!

You could select the 12 cells and name them by typing a name in the address box on the left hand side of the formula bar. You can then use the name in the formula, e.g. =SUM(MyCells).

Trust me for thinking I could leave the New Year on a high!!

That works great, one question though, for obvious reasons you can's drag this down, for example I have highlighted the 12 Holiday total cells for the first employee and called it emp1H, I have then done the same for Sickness, emp1H, but I have 60 employee's and 9 categories to account for. I don't mind doing the work but will an extra 540 Ranges, so to speak, slow the workbook up to a great degree?

[HansV]

That would be more work than it's worth. It should be possible to avoid enumerating the individual cells by using a SUMIF or SUMPRODUCT formula.

[steveh]

That would be more work than it's worth. It should be possible to avoid enumerating the individual cells by using a SUMIF or SUMPRODUCT formula.

Hi Hans

Thank you. I ended up doing ot the way that I first said =Planner!AR7+Planner!AR72+Planner!AR137+Planner!AR202+Planner!AR266+Planner!AR331+Planner!AR396+Planner!AR461+Planner!AR526+Planner!AR591+Planner!AR656+Planner!AR721 and then dragging it across and down the 60 columns which only toook a few minutes but I just though that there would be a more effecient or elegant way. But anyway, job done.

[HansV]

As I mentioned in my previous reply, it would probably be possible to create a SUMIF or SUMPRODUCT formula.

Just out of curiosity: from AR7 to AR72 is 65 rows, from AR72 to AR137 is 65 rows etc., with one exception: from AR202 to AR266 is only 64 rows. Why is that?

[steveh]

As I mentioned in my previous reply, it would probably be possible to create a SUMIF or SUMPRODUCT formula.

Just out of curiosity: from AR7 to AR72 is 65 rows, from AR72 to AR137 is 65 rows etc., with one exception: from AR202 to AR266 is only 64 rows. Why is that?

Sorry Hans I did not notice this reply

It was a msitake at set up stage and by the time the form was populated with formulas etc. I was scared to change it because normally it throws everything out for me so as it really did no harm I left it. I realise now (3 years later) that having a fixed 65 would have been sooo much easier when referencing from other sheets etc.