Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Overbey
 
Posts: n/a
Default 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


  #2   Report Post  
JulieD
 
Posts: n/a
Default

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




  #3   Report Post  
Overbey
 
Posts: n/a
Default

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






  #4   Report Post  
Overbey
 
Posts: n/a
Default

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






  #5   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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










  #6   Report Post  
Overbey
 
Posts: n/a
Default

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










  #7   Report Post  
Overbey
 
Posts: n/a
Default

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










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shared File & Freeze panes issue JM Excel Discussion (Misc queries) 0 January 21st 05 08:01 PM
Date issue between Windows and Macintosh version dlg1967 Excel Discussion (Misc queries) 4 January 19th 05 04:51 PM
Issue with copying Text. David Excel Discussion (Misc queries) 2 December 17th 04 01:39 AM
Autofill issue... nc0229 Excel Discussion (Misc queries) 0 December 13th 04 12:12 PM
addin loading issue howiefan Excel Discussion (Misc queries) 3 December 2nd 04 12:10 AM


All times are GMT +1. The time now is 12:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"