please help with this issue

  • Thread starter Thread starter Overbey
  • Start date Start date
O

Overbey

I have a workbook with approx. 25-30 worksheets in it with the last being an
overview of the others. I use this workbook to track some basketball stats.
What I need to do is to: countif cell A9 of sheet 1 thru sheet 30 are
populated with a text letter (P for played), the resultant of the countif
will be used to divide into an overall sum of points - thus giving an
average points per game.
Any suggestions
Thanks
Dudley
 
Hi Dudley

if you list the names of your sheets in an area of the summary worksheet
(e.g. A1:A30) you can use the following formula

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A30&"'!A9"),"P"))
(after the INDIRECT and bracket its a double quote("), then a single(') and
then another double and before the exclaimation mark it's a double followed
by a single)

if the sheets still have the default names e.g. Sheet1, Sheet2, etc then you
can use this formula

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:30"))&"'!A9"),"P"))

and you don't have to list out the names of the worksheets.

hope this helps

Cheers

JulieD
 
Thanks,
Will try
Dudley

JulieD said:
Hi Dudley

if you list the names of your sheets in an area of the summary worksheet
(e.g. A1:A30) you can use the following formula

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A30&"'!A9"),"P"))
(after the INDIRECT and bracket its a double quote("), then a single(') and
then another double and before the exclaimation mark it's a double followed
by a single)

if the sheets still have the default names e.g. Sheet1, Sheet2, etc then you
can use this formula

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:30"))&"'!A9"),"P"))

and you don't have to list out the names of the worksheets.

hope this helps

Cheers

JulieD
Click to expand...
 
I'm sorry, I may be thick headed, but what do you mean by: if you list the
names of your sheets in an area of the summary worksheet (e.g. A1:A30) you
can use the following formula ?
I do not follow - here.
Thanks
 
Put each sheet name in a cell, i.e. if your sheet names for instance would
be January to December you would create
a 12 cell range (assume you put it in A1:A12) and put January, February and
so on, then use that range in Julie's formula
replacing A1:A30 with A1:A12

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Thanks all
Dudley

Peo Sjoblom said:
Put each sheet name in a cell, i.e. if your sheet names for instance would
be January to December you would create
a 12 cell range (assume you put it in A1:A12) and put January, February and
so on, then use that range in Julie's formula
replacing A1:A30 with A1:A12

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
Click to expand...
 
Thanks all,
That worked "GREAT"
Dudley


Peo Sjoblom said:
Put each sheet name in a cell, i.e. if your sheet names for instance would
be January to December you would create
a 12 cell range (assume you put it in A1:A12) and put January, February and
so on, then use that range in Julie's formula
replacing A1:A30 with A1:A12

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
Click to expand...
 
Back
Top