I am attempting to create a summary sheet

[Liana]

I am trying to sum totals from 19 pages, I have entered 2 dummy sheets at the
beginning & end & now want monthly totals. each worksheet has monthly totals
but I need the overall month for the whole team, I tried sum(first:last cell
number) but the answer is zero - what am I missing ?

 

[David Biddulph]

Exactly what did your formula look like?

 

[Liana]

Exactly what did your formula look like?
--
David Biddulph



=SUM(first:lastB48), I also tried sum= () to which I clicked on every persons monthly total cell & hoped to get an answer - still no luck

 

[David Biddulph]

You missed out a small but important exclamation mark.
It should be =SUM(first:last!B48)

 

[Liana]

Thanks, I now have a #REF error in the cell ! should I always use the blank
pages at the beginning & end of the book ?

 

[Liana]

I have managed to get a total for one sum but if I try with the other 2
totals in the same way I still get a zero figure..

 

[David Biddulph]

Either you've got a different formula from what you think you've got, or
you've got different values in the input cells from what you think you've
got (and one possibility in the last category is that you've got text where
you think you've got numbers).

 

[Roger Govier]

Hi Liana

Your sheet order should be
Summary Sheet with your Sum formula
First (Blank)
Sheet1
..
..
Sheet19
Last (Blank)

The Summary sheet can be before First, or after Last, but should not be in
between.

 

[Liana]

I am now confused - where will the text be that should be numbers etc - how
do I check that ?

 

[Liana]

My workbook is laid out exactly that way , I am now beginning to give up just
when I thought I was getting the hang of it all !!

 

[Roger Govier]

Hi Liana

Don't give up, it will work.
If you want to mail me a copy of your workbook, I will take a look.
to mail direct send to
roger at technology4u dot co dot uk
Change the at and dots to the obvious.

 

[David Biddulph]

For each cell that you are trying to add, if it has a number in it then
=ISNUMBER(cell_ref) should return TRUE, and =ISTEXT(cell_ref) should return
FALSE. If the results are the other way round (TRUE for ISTEXT and false
for ISNUMBER), then you may well have problem using those cells in your SUM
formula.

One confusing feature of Excel is that if you have a cell containg =" 1 " or
a cell containing '100, those text values will be omitted by the SUM
formula, but if you had =cell_ref_1+cell_ref_2+cell_ref_3, then the numbers
(1 and 100 respectively) hidden in the text are included in the addition.

 

[Liana]

I am trying to work out a summary of times taken for tasks i have set up
formula of sum(first:last!WA35) which represents everyones time for training
in a month however it should be 27hours 20 mins but shows as 99:35:00 how do
I convert this to the correct time

 

[Liana]

I have a formula on my worksheet which is trying to calculate a weekly
average but the daily average is made up of 2 different percentages
=AVERAGE(C43,P37) the trouble is if there is a blank day eg a bank holiday
then the average is DIV#0!, I know I need to do an IF sum but how do I do it
when the 2 averages are added, I have tried to do first:last(cell no) but if
there is a blank day then I get no weekly total>
Can anyone help ?

 

[David Biddulph]

If you want to avoid the #DIV/0! problem, you could try
=IF(COUNT(C43,P37)=2,AVERAGE(C43,P37),"")