(moved from general) I am trying to take the data from the Races sheet and
have certain parts counted, summed, or averaged by track into the Track Stats
sheet. I'm really not sure how to go about starting this as it will need to
look through each line and determine if the results are from that track.
This is too complicated to explain thoroughly so I posted a link to the sheet
is below.

Link: http://www.zshare.net/download/1725482392a76ca7
It contains a couple of macros for determining sheet names etc...

For one explainable example, I am trying to have the Track Stats sheet look
through up to 5000 entries on the Races sheet and count the number of starts
for a given track based on the criteria that the range does not equal
0,"","B", or "DNS". I'm thinking it would be framed similar to this but it
isn't working for the whole range...

=IF(OR(COUNT(Races!$AG$2:$AG$5000)<0,COUNT(Races! $AG$2:$AG$5000)<"",COUNT(Races!$AG$2:$AG$5000)<" B",COUNT(Races!$AG$2:$AG$5000)<0)),"-",COUNT(Races!$AG$2:$AG$5000)),0)

Atlanta would be a good test case on the Track Stats sheets as it is the
first in the list that should pop up numbers. Any help or direction is
appreciated.

I'm looking at your file...

It's not real clear what you want to do based on your posted formula:

=IF(OR(COUNT(Races!$AG$2:$AG$5000)<0,COUNT(Races! $AG$2:$AG$5000)<"",COUNT(Races!$AG$2:$AG$5000)<" B",COUNT(Races!$AG$2:$AG$5000)<0)),"-",COUNT(Races!$AG$2:$AG$5000)),0)

My best guess is that you want to count only cells that are numbers and
greater than 0 but there are no zeros in that range.

So, try this:

=SUMPRODUCT(--(Tracks="Atlanta"),--(ISNUMBER(Finished)))

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
(moved from general) I am trying to take the data from the Races sheet and
have certain parts counted, summed, or averaged by track into the Track
Stats
sheet. I'm really not sure how to go about starting this as it will need
to
look through each line and determine if the results are from that track.
This is too complicated to explain thoroughly so I posted a link to the
sheet
is below.

Link: http://www.zshare.net/download/1725482392a76ca7
It contains a couple of macros for determining sheet names etc...

For one explainable example, I am trying to have the Track Stats sheet
look
through up to 5000 entries on the Races sheet and count the number of
starts
for a given track based on the criteria that the range does not equal
0,"","B", or "DNS". I'm thinking it would be framed similar to this but
it
isn't working for the whole range...

=IF(OR(COUNT(Races!$AG$2:$AG$5000)<0,COUNT(Races! $AG$2:$AG$5000)<"",COUNT(Races!$AG$2:$AG$5000)<" B",COUNT(Races!$AG$2:$AG$5000)<0)),"-",COUNT(Races!$AG$2:$AG$5000)),0)

Atlanta would be a good test case on the Track Stats sheets as it is the
first in the list that should pop up numbers. Any help or direction is
appreciated.

First, thanks for the help......

Yes, I am wanting to calculate, for each individual track, the relevant
stats. Your formula seemed to work pretty good to count the number of starts
at a given track. I copied the formula as

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

to each cell in column E but I am a bit perplexed that when I summed it at
the top of the Track Stats sheet with

=SUM(E$3:E$1002)

.....it added up 492 starts. The problem is that I know there is 517. Even
manually counted them. Not sure if 25 of the track names were entered
incorrectly or with spaces? Is there a way to make it so that you can only
choose a track on the Races sheet (column W) that is included in the list box?

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

=SUMPRODUCT(--(Tracks=$C9),--(ISNUMBER(Finished)))

Is that correct?




"T. Valko" wrote:
My best guess is that you want to count only cells that are numbers and
greater than 0 but there are no zeros in that range.

So, try this:

=SUMPRODUCT(--(Tracks="Atlanta"),--(ISNUMBER(Finished)))

--
Biff
Microsoft Excel MVP

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

That formula is *counting*.

=SUM(E$3:E$1002)

That formula is *summing*.

Those are entirely different formulas and can not be used to compare
results.

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

Not needed in this case.


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
First, thanks for the help......

Yes, I am wanting to calculate, for each individual track, the relevant
stats. Your formula seemed to work pretty good to count the number of
starts
at a given track. I copied the formula as

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

to each cell in column E but I am a bit perplexed that when I summed it at
the top of the Track Stats sheet with

=SUM(E$3:E$1002)

....it added up 492 starts. The problem is that I know there is 517.
Even
manually counted them. Not sure if 25 of the track names were entered
incorrectly or with spaces? Is there a way to make it so that you can
only
choose a track on the Races sheet (column W) that is included in the list
box?

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

=SUMPRODUCT(--(Tracks=$C9),--(ISNUMBER(Finished)))

Is that correct?




"T. Valko" wrote:
My best guess is that you want to count only cells that are numbers and
greater than 0 but there are no zeros in that range.

So, try this:

=SUMPRODUCT(--(Tracks="Atlanta"),--(ISNUMBER(Finished)))

--
Biff
Microsoft Excel MVP

Correct, at the top of the Track Stats sheet I need to SUM to the COUNT.
(E1) is a sum of the individual counts.

"T. Valko" wrote:

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

That formula is *counting*.

=SUM(E$3:E$1002)

That formula is *summing*.

Those are entirely different formulas and can not be used to compare
results.

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

Not needed in this case.


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
First, thanks for the help......

Yes, I am wanting to calculate, for each individual track, the relevant
stats. Your formula seemed to work pretty good to count the number of
starts
at a given track. I copied the formula as

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

to each cell in column E but I am a bit perplexed that when I summed it at
the top of the Track Stats sheet with

=SUM(E$3:E$1002)

....it added up 492 starts. The problem is that I know there is 517.
Even
manually counted them. Not sure if 25 of the track names were entered
incorrectly or with spaces? Is there a way to make it so that you can
only
choose a track on the Races sheet (column W) that is included in the list
box?

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

=SUMPRODUCT(--(Tracks=$C9),--(ISNUMBER(Finished)))

Is that correct?




"T. Valko" wrote:
My best guess is that you want to count only cells that are numbers and
greater than 0 but there are no zeros in that range.

So, try this:

=SUMPRODUCT(--(Tracks="Atlanta"),--(ISNUMBER(Finished)))

--
Biff
Microsoft Excel MVP

I think you are misunderstanding what I was doing to quality control the
results. I know COUNT and SUM are two different things. However, I used sum
to add up the COUNTS from you formula, and then compared them to the number I
counted manually. I counted 517 manual, and the SUM of the COUNTS totalled
492. Hence I am trying to figure out where the discrepancy is and if I can
control for it. That was the reason I asked this:

Is there a way to make it so that you can only choose a track on the Races
sheet (column W) that is included in the list box?

"T. Valko" wrote:

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

That formula is *counting*.

=SUM(E$3:E$1002)

That formula is *summing*.

Those are entirely different formulas and can not be used to compare
results.

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

Not needed in this case.


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
First, thanks for the help......

Yes, I am wanting to calculate, for each individual track, the relevant
stats. Your formula seemed to work pretty good to count the number of
starts
at a given track. I copied the formula as

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

to each cell in column E but I am a bit perplexed that when I summed it at
the top of the Track Stats sheet with

=SUM(E$3:E$1002)

....it added up 492 starts. The problem is that I know there is 517.
Even
manually counted them. Not sure if 25 of the track names were entered
incorrectly or with spaces? Is there a way to make it so that you can
only
choose a track on the Races sheet (column W) that is included in the list
box?

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

=SUMPRODUCT(--(Tracks=$C9),--(ISNUMBER(Finished)))

Is that correct?




"T. Valko" wrote:
My best guess is that you want to count only cells that are numbers and
greater than 0 but there are no zeros in that range.

So, try this:

=SUMPRODUCT(--(Tracks="Atlanta"),--(ISNUMBER(Finished)))

--
Biff
Microsoft Excel MVP

Ok, I see what you mean.

There are probably track names that don't exactly match on the Races sheet
and the Track Stats sheet.. You'd have to make sure the track names on both
(all) sheets match exactly. You have too much data for me to look for the
mismatches. That'll be your job. <g

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Correct, at the top of the Track Stats sheet I need to SUM to the COUNT.
(E1) is a sum of the individual counts.

"T. Valko" wrote:

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

That formula is *counting*.

=SUM(E$3:E$1002)

That formula is *summing*.

Those are entirely different formulas and can not be used to compare
results.

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

Not needed in this case.


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
First, thanks for the help......

Yes, I am wanting to calculate, for each individual track, the relevant
stats. Your formula seemed to work pretty good to count the number of
starts
at a given track. I copied the formula as

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

to each cell in column E but I am a bit perplexed that when I summed it
at
the top of the Track Stats sheet with

=SUM(E$3:E$1002)

....it added up 492 starts. The problem is that I know there is 517.
Even
manually counted them. Not sure if 25 of the track names were entered
incorrectly or with spaces? Is there a way to make it so that you can
only
choose a track on the Races sheet (column W) that is included in the
list
box?

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

=SUMPRODUCT(--(Tracks=$C9),--(ISNUMBER(Finished)))

Is that correct?




"T. Valko" wrote:
My best guess is that you want to count only cells that are numbers
and
greater than 0 but there are no zeros in that range.

So, try this:

=SUMPRODUCT(--(Tracks="Atlanta"),--(ISNUMBER(Finished)))

--
Biff
Microsoft Excel MVP

Another question relating to this as I have been trying to learn
SUMPRODUCT.....
For Top 5s I tired =SUMPRODUCT(--(Tracks=$C23),--(Finished<=5)) and that did
not work as the blank cells on the race sheet are all showing 4,394 even
though I was thinking they would show zero. Any ideas on how to formulate
this.

BTW, I found the issue with the Tracks having extra spaces after the name -
it now has 517 starts as it should.

New sheet at:
http://www.zshare.net/download/173758079330c392/

Thanks for all of your help!

For Top 5s I tired =SUMPRODUCT(--(Tracks=$C23),--(Finished<=5))

Just add another test:

=SUMPRODUCT(--(Tracks=$C23),--(Finished=1),--(Finished<=5))

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Another question relating to this as I have been trying to learn
SUMPRODUCT.....
For Top 5s I tired =SUMPRODUCT(--(Tracks=$C23),--(Finished<=5)) and that
did
not work as the blank cells on the race sheet are all showing 4,394 even
though I was thinking they would show zero. Any ideas on how to formulate
this.

BTW, I found the issue with the Tracks having extra spaces after the
name -
it now has 517 starts as it should.

New sheet at:
http://www.zshare.net/download/173758079330c392/

Thanks for all of your help!

Ah, I should have been able to figure that one out. This one may be a bit
more difficult. Is it possible to calculate a Median for a Track with
SUMPRODUCT (as in column AC)

"T. Valko" wrote:

For Top 5s I tired =SUMPRODUCT(--(Tracks=$C23),--(Finished<=5))

Just add another test:

=SUMPRODUCT(--(Tracks=$C23),--(Finished=1),--(Finished<=5))

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Another question relating to this as I have been trying to learn
SUMPRODUCT.....
For Top 5s I tired =SUMPRODUCT(--(Tracks=$C23),--(Finished<=5)) and that
did
not work as the blank cells on the race sheet are all showing 4,394 even
though I was thinking they would show zero. Any ideas on how to formulate
this.

BTW, I found the issue with the Tracks having extra spaces after the
name -
it now has 517 starts as it should.

New sheet at:
http://www.zshare.net/download/173758079330c392/

Thanks for all of your help!

Try this array formula** :

=MEDIAN(IF(Tracks=C3,Finished))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Ah, I should have been able to figure that one out. This one may be a bit
more difficult. Is it possible to calculate a Median for a Track with
SUMPRODUCT (as in column AC)

"T. Valko" wrote:

For Top 5s I tired =SUMPRODUCT(--(Tracks=$C23),--(Finished<=5))

Just add another test:

=SUMPRODUCT(--(Tracks=$C23),--(Finished=1),--(Finished<=5))

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Another question relating to this as I have been trying to learn
SUMPRODUCT.....
For Top 5s I tired =SUMPRODUCT(--(Tracks=$C23),--(Finished<=5)) and
that
did
not work as the blank cells on the race sheet are all showing 4,394
even
though I was thinking they would show zero. Any ideas on how to
formulate
this.

BTW, I found the issue with the Tracks having extra spaces after the
name -
it now has 517 starts as it should.

New sheet at:
http://www.zshare.net/download/173758079330c392/

Thanks for all of your help!

Hmmm, I tried this and I get a #NUM! error - do you get the same? BTW, I did
use CTRL,SHIFT,ENTER. Then tried without it and it just gave me a zero.

"T. Valko" wrote:

Try this array formula** :

=MEDIAN(IF(Tracks=C3,Finished))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

You would get that is you have text in the equivalent of the range Biff
called Finished

--


Regards,


Peo Sjoblom

"Brian" wrote in message
...
Hmmm, I tried this and I get a #NUM! error - do you get the same? BTW, I
did
use CTRL,SHIFT,ENTER. Then tried without it and it just gave me a zero.

"T. Valko" wrote:

Try this array formula** :

=MEDIAN(IF(Tracks=C3,Finished))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

MEDIAN ignores text.


--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
You would get that is you have text in the equivalent of the range Biff
called Finished

--


Regards,


Peo Sjoblom

"Brian" wrote in message
...
Hmmm, I tried this and I get a #NUM! error - do you get the same? BTW, I
did
use CTRL,SHIFT,ENTER. Then tried without it and it just gave me a zero.

"T. Valko" wrote:

Try this array formula** :

=MEDIAN(IF(Tracks=C3,Finished))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

If there are no cells that meet the criteria then you'll get that error.

In your updated file, I enter the array formula** in AC3 and copy down to
AC15. Out of all those cells only 2 return a number.

Atlanta = 2
Bathurst = 8.5

All the others return #NUM! because there is no data for those tracks.

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Hmmm, I tried this and I get a #NUM! error - do you get the same? BTW, I
did
use CTRL,SHIFT,ENTER. Then tried without it and it just gave me a zero.

"T. Valko" wrote:

Try this array formula** :

=MEDIAN(IF(Tracks=C3,Finished))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

Not if all the values are text

--


Regards,


Peo Sjoblom

"T. Valko" wrote in message
...
MEDIAN ignores text.


--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
You would get that is you have text in the equivalent of the range Biff
called Finished

--


Regards,


Peo Sjoblom

"Brian" wrote in message
...
Hmmm, I tried this and I get a #NUM! error - do you get the same? BTW,
I did
use CTRL,SHIFT,ENTER. Then tried without it and it just gave me a zero.

"T. Valko" wrote:

Try this array formula** :

=MEDIAN(IF(Tracks=C3,Finished))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

Clarification:

MEDIAN ignores text.

However, if the range contains nothing but text then you'll get the #NUM!
error.

But that is not the case in this situation. There are both numbers and text
in the range and in this case the text is ignored.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
MEDIAN ignores text.


--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
You would get that is you have text in the equivalent of the range Biff
called Finished

--


Regards,


Peo Sjoblom

"Brian" wrote in message
...
Hmmm, I tried this and I get a #NUM! error - do you get the same? BTW,
I did
use CTRL,SHIFT,ENTER. Then tried without it and it just gave me a zero.

"T. Valko" wrote:

Try this array formula** :

=MEDIAN(IF(Tracks=C3,Finished))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

I just posted a clarification at the same time you posted this.

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
Not if all the values are text

--


Regards,


Peo Sjoblom

"T. Valko" wrote in message
...
MEDIAN ignores text.


--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
You would get that is you have text in the equivalent of the range Biff
called Finished

--


Regards,


Peo Sjoblom

"Brian" wrote in message
...
Hmmm, I tried this and I get a #NUM! error - do you get the same? BTW,
I did
use CTRL,SHIFT,ENTER. Then tried without it and it just gave me a
zero.

"T. Valko" wrote:

Try this array formula** :

=MEDIAN(IF(Tracks=C3,Finished))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

Yes, Peo, some cells have text, but Biff is right - MEDIAN ignores text. I
guess the next logical question is how best to frame the formula so that
tracks without data get a "-".


"T. Valko" wrote:

If there are no cells that meet the criteria then you'll get that error.

In your updated file, I enter the array formula** in AC3 and copy down to
AC15. Out of all those cells only 2 return a number.

Atlanta = 2
Bathurst = 8.5

All the others return #NUM! because there is no data for those tracks.

--
Biff
Microsoft Excel MVP

Something to consider...

Your file is already "kind of big", 5mb, and it's already exhibiting slow
calculation times. I see all those other columns where you intend to do
calculations and it looks like most of them are going to end up being array
formulas. So, you need to start making things as efficient as possible. With
that in mind:

=IF(SUMIF(Tracks,C3,Finished),MEDIAN(IF(Tracks=C3, Finished)),"-")

array entered

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Yes, Peo, some cells have text, but Biff is right - MEDIAN ignores text.
I
guess the next logical question is how best to frame the formula so that
tracks without data get a "-".


"T. Valko" wrote:

If there are no cells that meet the criteria then you'll get that error.

In your updated file, I enter the array formula** in AC3 and copy down to
AC15. Out of all those cells only 2 return a number.

Atlanta = 2
Bathurst = 8.5

All the others return #NUM! because there is no data for those tracks.

--
Biff
Microsoft Excel MVP

That's for sure! Thanks - that worked.

"T. Valko" wrote:

Something to consider...

Your file is already "kind of big", 5mb, and it's already exhibiting slow
calculation times. I see all those other columns where you intend to do
calculations and it looks like most of them are going to end up being array
formulas. So, you need to start making things as efficient as possible. With
that in mind:

=IF(SUMIF(Tracks,C3,Finished),MEDIAN(IF(Tracks=C3, Finished)),"-")

array entered

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Yes, Peo, some cells have text, but Biff is right - MEDIAN ignores text.
I
guess the next logical question is how best to frame the formula so that
tracks without data get a "-".


"T. Valko" wrote:

If there are no cells that meet the criteria then you'll get that error.

In your updated file, I enter the array formula** in AC3 and copy down to
AC15. Out of all those cells only 2 return a number.

Atlanta = 2
Bathurst = 8.5

All the others return #NUM! because there is no data for those tracks.

--
Biff
Microsoft Excel MVP

Latest file at http://www.zshare.net/download/17461315deb0bf58/ I was able
to tidy it up a bit to make it a little faster.

I have been able to do several of the cell formulas based upon the logic and
examples you have given me. However, 3 columns of them are still eating me
alive and after spending several hours trying different formulas found all
over the net, I finally got wise and came back here!

The columns that are giving me issues are (W,Z, & AD) aka (Event Miles, Best
Qual, & Best Finish) on the Track Stats sheet.

Event Miles are giving me an issue because I am getting an #N/A? error for
Tracks that are blank. I tried various ways of using ISNA but none worked
right.

Best Qual & Finish will both use a similar formula. The main issue again
is blank cells which are causing it to think my best finishes are 0 instead
of some other number. There are a few examples on the net, but I didn't get
any of them to work satisfactorily.

Do you have any suggestions.

Once I get this completed I should be just about done except for wanting to
compute the stats by 'Car Type' instead of just overall (see cell A1 on track
Stats). I believe that's likely going to require me to redo virtually all of
the formulas to add another condition in the Stats sheet.

That link doesn't work.

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
Latest file at http://www.zshare.net/download/17461315deb0bf58/ I was
able
to tidy it up a bit to make it a little faster.

I have been able to do several of the cell formulas based upon the logic
and
examples you have given me. However, 3 columns of them are still eating
me
alive and after spending several hours trying different formulas found all
over the net, I finally got wise and came back here!

The columns that are giving me issues are (W,Z, & AD) aka (Event Miles,
Best
Qual, & Best Finish) on the Track Stats sheet.

Event Miles are giving me an issue because I am getting an #N/A? error for
Tracks that are blank. I tried various ways of using ISNA but none worked
right.

Best Qual & Finish will both use a similar formula. The main issue again
is blank cells which are causing it to think my best finishes are 0
instead
of some other number. There are a few examples on the net, but I didn't
get
any of them to work satisfactorily.

Do you have any suggestions.

Once I get this completed I should be just about done except for wanting
to
compute the stats by 'Car Type' instead of just overall (see cell A1 on
track
Stats). I believe that's likely going to require me to redo virtually all
of
the formulas to add another condition in the Stats sheet.