please help with this issue
 

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

"Overbey" wrote in message
...

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

Thanks,
Will try
Dudley

"JulieD" wrote in message
...
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

"Overbey" wrote in message
...

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

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


"JulieD" wrote in message
...
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

"Overbey" wrote in message
...

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

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)



"Overbey" wrote in message
...
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


"JulieD" wrote in message
...
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

"Overbey" wrote in message
...

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

Thanks all
Dudley

"Peo Sjoblom" wrote in message
...
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)



"Overbey" wrote in message
...
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


"JulieD" wrote in message
...
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

"Overbey" wrote in message
...

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

Thanks all,
That worked "GREAT"
Dudley


"Peo Sjoblom" wrote in message
...
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)



"Overbey" wrote in message
...
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


"JulieD" wrote in message
...
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

"Overbey" wrote in message
...

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