Descending Numbers

[Ron Rosenfeld]

Thanks Ron, sorry but the list was illustrative of the single entry in
the time field and therefore the entries in the form field were not
all yellow.....

I have added the formula in as you suggest but instead of getting a
count of 1, 2,3 etc i get the following

Date Time Count
25-Jun 13:45:00 105
25-Jun 13:45:00 105
25-Jun 13:45:00 105
25-Jun 14:00:00 284
25-Jun 14:00:00 284
25-Jun 14:05:00 97
25-Jun 14:05:00 97
25-Jun 14:05:00 97


Isa this because it is counting the time field which is formatted in
hh:mm:ss ?

Thanks again Ron

Sam

Sam,

It does count the time field (that is the Column B in the formula). From your data, that would seem to be the easiest way to determine that there is only a single horse in the list. But it is not checking the date.

Does your data have entries for more than one date?

If so, try this formula instead:

=COUNTIFS($B:$B,B2,$A:$A,A2)

Note: I am assuming, since you can filter by color, that you have Excel 2007 or later. Earlier versions do not have this function and we'd need a different formula.

Since your data has entries from multiple tracks, I'm surprised you don't have an identifying column in your data to indicate which track the data refers to. Obviously, if you are interested in whether a given race has only a single "hit", unless races at different tracks never have the same time, there's no way to do that.

 

[Sam Harman]

Sam,

It does count the time field (that is the Column B in the formula). From your data, that would seem to be the easiest way to determine that there is only a single horse in the list. But it is not checking the date.

Does your data have entries for more than one date?

If so, try this formula instead:

=COUNTIFS($B:$B,B2,$A:$A,A2)

Note: I am assuming, since you can filter by color, that you have Excel 2007 or later. Earlier versions do not have this function and we'd need a different formula.

Since your data has entries from multiple tracks, I'm surprised you don't have an identifying column in your data to indicate which track the data refers to. Obviously, if you are interested in whether a given race has only a single "hit", unless races at different tracks never have the same time, there's no way to do that.

Hi Ron, the new formula correctly counts the number of entries for
each of the time slots but when filter the number stays the same. See
below:

Before filtering on the form column for yellow



25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8

After filtering the form colum for yellow progressive horses

25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 14:00:00 8
25-Jun 14:00:00 8


Thanks

Sam

 

[Ron Rosenfeld]

Hi Ron, the new formula correctly counts the number of entries for
each of the time slots but when filter the number stays the same. See
below:

Before filtering on the form column for yellow



25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8

After filtering the form colum for yellow progressive horses

25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 14:00:00 8
25-Jun 14:00:00 8


Thanks

Sam

OK so I guess it doesn't matter that you might have multiple races at different tracks starting at the same time.

COUNTIFS is not sensitive to filtered rows. You have to use a different method of getting at it.

Instead of the countifs formula, try this:

D2:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$1,ROW(INDIRECT("1:"&-1+COUNTA($A:$A))),0,1))*
(A2=OFFSET($A$1,1,0,-1+COUNTA($A:$A),1))*(B2=OFFSET($B$1,1,0,-1+COUNTA($B:$B),1)))

It is CRITICAL that there be no blank lines in the data range.

After you filter by color, select in the filter to retun only 1

 

[Sam Harman]

OK so I guess it doesn't matter that you might have multiple races at different tracks starting at the same time.

COUNTIFS is not sensitive to filtered rows. You have to use a different method of getting at it.

Instead of the countifs formula, try this:

D2:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$1,ROW(INDIRECT("1:"&-1+COUNTA($A:$A))),0,1))*
(A2=OFFSET($A$1,1,0,-1+COUNTA($A:$A),1))*(B2=OFFSET($B$1,1,0,-1+COUNTA($B:$B),1)))

It is CRITICAL that there be no blank lines in the data range.

After you filter by color, select in the filter to retun only 1

Hi Ron, I have been on goliday for the last three weeks so this is the
first opportunity I have had to thank you so much for all the help you
have given me with my spreadsheet. I still have a few more niggling
questions which I hope there is a solution to but I will give you a
break before I ask if thats alright.

Thanks again you have been a fantastic help

Cheers

Sam