Summing same cell on multiple worksheets

**Aussie_Striker** · 01-13-2007, 09:55 PM

I know it can be done because I recently did a course and found it, however I cannot get it to work. I have tried
=sum(Start!BO15:End!BO15) value error (could it just be what it is summing?)
=sum(Start!:End!BO15)
=sum(Start!:End!,BO15)

none of which work correctly unless the first option is a problem with the cells being summed.

**MSP77079** · 01-13-2007, 11:02 PM

I would not try to remember the formula. Remember how to create it. Which is:
1. Type =Sum(
2. click on the first worksheet and there the first cell
3. hold the Shift key
4. click on the TAB of the last worksheet

Doing that, I got:

=SUM(Sheet1:Sheet3!$B$13)

**MSP77079** · 01-13-2007, 11:06 PM

better yet ... remember the built-in help. I did a search on "3d" and one of the choices is "Refer to the same cell or range on multiple sheets". That will remind you how to do this if you forget.

**Aussie_Striker** · 01-14-2007, 12:10 AM

I had tried getting it the same way. Got it to work now. had an '!' where it shouldnt be and now works.
Now to go a step further I need it to get the figure off multiple sheets with criteria ie

=COUNTIF(Start:End!BF17,">.01")

unfortunately I'm back to getting a #value error again
with
=COUNT(Start:End!BF17)
I get a result but it counts 0's as well which I do not want.

I also tried
=COUNTIF(Start:End!BF17,"<>0")

Used with the insert function but still cound't get it to work properly

**Carim** · 01-14-2007, 04:01 AM

Hi,

Unfortunately, COUNTIF and SUMIF don't work with 3D references.
Assuming you have 10 sheets (from Start to End), could following help ...

Please Login or Register  to view this content.

Otherwise, the solution is to use Laurent Longre 's add-in : MoreFunc.xll
http://xcell05.free.fr/
which contains a COUNTIF.3D function

**Aussie_Striker** · 01-14-2007, 06:03 AM

I had written another post but it doesn't seem to have worked. Yes I found that I couldn't use countif so I adjusted my range that I was looking at and used sum instead.