summing a single cell across multiple worksheets

  • Thread starter Thread starter gr8guy
  • Start date Start date
G

gr8guy

Hi,

I hv 31 worksheets & on every sheet there is a cell C1 on top, which shows
the total SUM for a particular column range e.g. C9:C69.

since all sheets r similar, the ranges r the same, & the cell C1 which shows
the total, is the same on every worksheet.

1) Is there a formula which can be entered in a 'final_totals_sheet', which
will show the summation of the total values in C1 across the 31 sheets?

(3D Range idea courtesy of Harlan Grove....thanks to him!)

Defined a range as for 31 sheets as 1.....31 named Shts =
final_totals_sheet!$A$1:$A$E1.
Defined another range as Rngs = "'"&Shts&"'!C1"


Is there a formula something like this which can calculate the total of C1's
in 31 worksheets?

=sum(indirect(Rngs))

Note, this is not working for me! Pls Help!

2) Also want to knw how to get a unique count of no of people in a column
range even if the range includes blanks!
e.g. a9:a69 contains some of the names from a9:a15 :

eijaz
zaki
aireen
eijaz
aireen
mumma

here the unique entries count would be : 4 v., eijaz, zaki, aireen, mumma.


I am trying to use :

=SUM(IF(FREQUENCY(MATCH(A9:A69,A9:A69,0),MATCH(A9:A69,A9:A69,0))>0,1))

but whenever there is are blank cells on top or in between, or the last
cells in the column range are not filled, it gives #N/A error. Can't make a
dynamic range, as there are 31 sheets each having a long specified range
a9:a69. How to avoid this error, so that it can count unique entries even if
there are blank cells in between or the range is not fully filled.

Your inputs would be highly appreciated!

Rgds,

Eijaz
 
I hv 31 worksheets & on every sheet there is a cell C1 on top, which shows
the total SUM for a particular column range e.g. C9:C69.
Click to expand...

Insert two new sheets, name them First and Last, put all 31 sheets between
First and Last, and use:

=SUM(First:Last!C1)
...Also want to knw how to get a unique count of no of people in a column
range even if the range includes blanks!
e.g. a9:a69 contains some of the names from a9:a15 :
Click to expand...

=SUMPRODUCT((A9:A69<>"")/COUNTIF(A9:A69,A9:A69&""))
 
Back
Top