software

Howdy All,

This is what I want to do:

I have a spreadsheet with 4 dates in columns I, J, K, and L starting in row
3.

In the corresponding columns of row 1, i want to count the number of times
each column contains the most recent date.

Example:

I3 = 1/1/2000, J3 = 2/2/2001, K3 = 3/3/2002, L3 = 4/4/2003

L3 would now equal 1.

Then next time column L contains the most recent date, L3 would enumerate
and equal 2, etc.

Any ideas?

Thanks,
Brian
Try something like this in cell I1:

=COUNTIF(I3:I100,MAX($I$3:$L$3))

then copy to cells J1, K1 and L1. This assumes you will have up to 100
items in each column - adjust to suit.

Hope this helps.

PeteHi!

If I understand correctly......

Enter this formula in I1 and copy across to L1:

=SUMPRODUCT(--(I3:I7=SUBTOTAL(4,OFFSET($I3:$L7,ROW(I3:L7)-ROW(I3:L3),,1))))

Adjust ranges to suit.

Biff

quot;Brianquot; gt; wrote in message
...
gt; Howdy All,
gt;
gt; This is what I want to do:
gt;
gt; I have a spreadsheet with 4 dates in columns I, J, K, and L starting in
gt; row
gt; 3.
gt;
gt; In the corresponding columns of row 1, i want to count the number of times
gt; each column contains the most recent date.
gt;
gt; Example:
gt;
gt; I3 = 1/1/2000, J3 = 2/2/2001, K3 = 3/3/2002, L3 = 4/4/2003
gt;
gt; L3 would now equal 1.
gt;
gt; Then next time column L contains the most recent date, L3 would enumerate
gt; and equal 2, etc.
gt;
gt; Any ideas?
gt;
gt; Thanks,
gt; Brian
gt;
gt;
Thanks Pete.

But, I'm not sure that this is doing what I want.
I want to compare the 4 values in each successive rows against the values in
just that row.

Example: I3 thru L3 are compare just against I3 thru L3 and the column
containing the most recent date enumerates, I4 thru L4 are compared just
against I4 thru L4 and the column containing the most recent date
enumerates.

Is that what you formula does?

Thanks,
Brian

quot;Pete_UKquot; gt; wrote in message ups.com...
gt; Try something like this in cell I1:
gt;
gt; =COUNTIF(I3:I100,MAX($I$3:$L$3))
gt;
gt; then copy to cells J1, K1 and L1. This assumes you will have up to 100
gt; items in each column - adjust to suit.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;Is that what you formula does?

That's what my formula does. Did you try it?

Biff

quot;Brianquot; gt; wrote in message
...
gt; Thanks Pete.
gt;
gt; But, I'm not sure that this is doing what I want.
gt; I want to compare the 4 values in each successive rows against the values
gt; in just that row.
gt;
gt; Example: I3 thru L3 are compare just against I3 thru L3 and the column
gt; containing the most recent date enumerates, I4 thru L4 are compared just
gt; against I4 thru L4 and the column containing the most recent date
gt; enumerates.
gt;
gt; Is that what you formula does?
gt;
gt; Thanks,
gt; Brian
gt;
gt; quot;Pete_UKquot; gt; wrote in message
gt; ups.com...
gt;gt; Try something like this in cell I1:
gt;gt;
gt;gt; =COUNTIF(I3:I100,MAX($I$3:$L$3))
gt;gt;
gt;gt; then copy to cells J1, K1 and L1. This assumes you will have up to 100
gt;gt; items in each column - adjust to suit.
gt;gt;
gt;gt; Hope this helps.
gt;gt;
gt;gt; Pete
gt;gt;
gt;
gt;
Biff,

I tried your formula as well, but didn't get the results I expected.

I adjust it, because I have 4300 values which from row 3 thru row 4303.quot;Biffquot; gt; wrote in message
...
gt; gt;Is that what you formula does?
gt;
gt; That's what my formula does. Did you try it?
gt;
gt; Biff
gt;
gt; quot;Brianquot; gt; wrote in message
gt; ...
gt;gt; Thanks Pete.
gt;gt;
gt;gt; But, I'm not sure that this is doing what I want.
gt;gt; I want to compare the 4 values in each successive rows against the values
gt;gt; in just that row.
gt;gt;
gt;gt; Example: I3 thru L3 are compare just against I3 thru L3 and the column
gt;gt; containing the most recent date enumerates, I4 thru L4 are compared just
gt;gt; against I4 thru L4 and the column containing the most recent date
gt;gt; enumerates.
gt;gt;
gt;gt; Is that what you formula does?
gt;gt;
gt;gt; Thanks,
gt;gt; Brian
gt;gt;
gt;gt; quot;Pete_UKquot; gt; wrote in message
gt;gt; ups.com...
gt;gt;gt; Try something like this in cell I1:
gt;gt;gt;
gt;gt;gt; =COUNTIF(I3:I100,MAX($I$3:$L$3))
gt;gt;gt;
gt;gt;gt; then copy to cells J1, K1 and L1. This assumes you will have up to 100
gt;gt;gt; items in each column - adjust to suit.
gt;gt;gt;
gt;gt;gt; Hope this helps.
gt;gt;gt;
gt;gt;gt; Pete
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
Post the *EXACT* formula you used.

What is your *EXACT* range, I3:L4303 ?

Biff

quot;Brianquot; gt; wrote in message
...
gt; Biff,
gt;
gt; I tried your formula as well, but didn't get the results I expected.
gt;
gt; I adjust it, because I have 4300 values which from row 3 thru row 4303.
gt;
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt;gt; gt;Is that what you formula does?
gt;gt;
gt;gt; That's what my formula does. Did you try it?
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Brianquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Thanks Pete.
gt;gt;gt;
gt;gt;gt; But, I'm not sure that this is doing what I want.
gt;gt;gt; I want to compare the 4 values in each successive rows against the
gt;gt;gt; values in just that row.
gt;gt;gt;
gt;gt;gt; Example: I3 thru L3 are compare just against I3 thru L3 and the column
gt;gt;gt; containing the most recent date enumerates, I4 thru L4 are compared just
gt;gt;gt; against I4 thru L4 and the column containing the most recent date
gt;gt;gt; enumerates.
gt;gt;gt;
gt;gt;gt; Is that what you formula does?
gt;gt;gt;
gt;gt;gt; Thanks,
gt;gt;gt; Brian
gt;gt;gt;
gt;gt;gt; quot;Pete_UKquot; gt; wrote in message
gt;gt;gt; ups.com...
gt;gt;gt;gt; Try something like this in cell I1:
gt;gt;gt;gt;
gt;gt;gt;gt; =COUNTIF(I3:I100,MAX($I$3:$L$3))
gt;gt;gt;gt;
gt;gt;gt;gt; then copy to cells J1, K1 and L1. This assumes you will have up to 100
gt;gt;gt;gt; items in each column - adjust to suit.
gt;gt;gt;gt;
gt;gt;gt;gt; Hope this helps.
gt;gt;gt;gt;
gt;gt;gt;gt; Pete
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
Do you have any rows where every cell is empty?

For example: (I understand that you're counting dates. Dates are really just
numbers formatted to look like a date so the formula will work on the below
example as well as a table full of dates)

...I..........J..........K..........L.....
10........22........14.........57
.........................................
44........19........88.........77

If so, each empty cell will evaluate to being the max value of that
particular row. Empty cells evaluate to 0 and since there is no value higher
than 0, 0 is the max value for that row.

This formula will account for empty cells:

=SUMPRODUCT(--(I3:I7lt;gt;quot;quot;),--(I3:I7=SUBTOTAL(4,OFFSET($I3:$L7,ROW(I3:L7)-ROW(I3:L3),,1))))

Biff

quot;Biffquot; gt; wrote in message
...
gt; Post the *EXACT* formula you used.
gt;
gt; What is your *EXACT* range, I3:L4303 ?
gt;
gt; Biff
gt;
gt; quot;Brianquot; gt; wrote in message
gt; ...
gt;gt; Biff,
gt;gt;
gt;gt; I tried your formula as well, but didn't get the results I expected.
gt;gt;
gt;gt; I adjust it, because I have 4300 values which from row 3 thru row 4303.
gt;gt;
gt;gt;
gt;gt; quot;Biffquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; gt;Is that what you formula does?
gt;gt;gt;
gt;gt;gt; That's what my formula does. Did you try it?
gt;gt;gt;
gt;gt;gt; Biff
gt;gt;gt;
gt;gt;gt; quot;Brianquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt; Thanks Pete.
gt;gt;gt;gt;
gt;gt;gt;gt; But, I'm not sure that this is doing what I want.
gt;gt;gt;gt; I want to compare the 4 values in each successive rows against the
gt;gt;gt;gt; values in just that row.
gt;gt;gt;gt;
gt;gt;gt;gt; Example: I3 thru L3 are compare just against I3 thru L3 and the column
gt;gt;gt;gt; containing the most recent date enumerates, I4 thru L4 are compared
gt;gt;gt;gt; just against I4 thru L4 and the column containing the most recent date
gt;gt;gt;gt; enumerates.
gt;gt;gt;gt;
gt;gt;gt;gt; Is that what you formula does?
gt;gt;gt;gt;
gt;gt;gt;gt; Thanks,
gt;gt;gt;gt; Brian
gt;gt;gt;gt;
gt;gt;gt;gt; quot;Pete_UKquot; gt; wrote in message
gt;gt;gt;gt; ups.com...
gt;gt;gt;gt;gt; Try something like this in cell I1:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; =COUNTIF(I3:I100,MAX($I$3:$L$3))
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; then copy to cells J1, K1 and L1. This assumes you will have up to 100
gt;gt;gt;gt;gt; items in each column - adjust to suit.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Hope this helps.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Pete
gt;gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;