A1=5 B1=Red
A2=7 B2=Blue
A3=2 B3=Red
A4=4 B4=Red
A5=6 B5=Blue

I know if I have SUBTOTAL(4,A1:A5) the result will be 7, but how can I
specify I only want the SUBTOTAL function to consider items in the range
that match a corresponding value in the B column. For example if I have the
same function, SUBTOTAL(4,A1:A5) but I want it only to look at corresponding
Red values such that the result should be 5 as it would only be looking at
A1, A3 & A4. This would permit me to find the highest value in the range of
A1:A5 that were of Red.

Is there a reason you didn't use the simpler:
=max(a1:a5)

If there is no reason, maybe you could just use:
=MAX(IF(B1:B5="red",A1:A5))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

You may want to consider using a pivottable.

You'll be able find all the maximums without writing individual formulas.

"M.Siler" wrote:

A1=5 B1=Red
A2=7 B2=Blue
A3=2 B3=Red
A4=4 B4=Red
A5=6 B5=Blue

I know if I have SUBTOTAL(4,A1:A5) the result will be 7, but how can I
specify I only want the SUBTOTAL function to consider items in the range
that match a corresponding value in the B column. For example if I have the
same function, SUBTOTAL(4,A1:A5) but I want it only to look at corresponding
Red values such that the result should be 5 as it would only be looking at
A1, A3 & A4. This would permit me to find the highest value in the range of
A1:A5 that were of Red.

--

Dave Peterson

A B C
1 Count Team Month
2 5 Red January
3 7 Blue January
4 12 TOTAL
5 3 Red February
6 4 Red February
7 6 Blue February
8 13 TOTAL
9
10 #VALUE! Max for RED
11 #VALUE! Max for BLUE

Where I defined a name range for the Teams as TeamRange =B2:B3,B5:B7
Where I defined a name range for the Count as CountRange =A2:A3,A5:A7

I tried the formula in A10 as =MAX(IF(TeamRange="Red",CountRange))
I tried the formula in A11 as =MAX(IF(TeamRange="Blue",CountRange))
I entered the formula as an array with Ctrl+Shift+Enter but I get #VALUE! as
it doesn't like the fact the range is not contiguous.

To answer the question about using SUBTOTAL and MAX was because I have
turned on DataFilterAutoFilter and I want the values calculated by the
formula to only calculate on the visible items.

It might be easier if I was to post the workbook on my website... I'll
include the link in the next post -- in about 15 minutes from this one.

Thanks!


"Dave Peterson" wrote in message
...
Is there a reason you didn't use the simpler:
=max(a1:a5)

If there is no reason, maybe you could just use:
=MAX(IF(B1:B5="red",A1:A5))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

You may want to consider using a pivottable.

You'll be able find all the maximums without writing individual formulas.

"M.Siler" wrote:

A1=5 B1=Red
A2=7 B2=Blue
A3=2 B3=Red
A4=4 B4=Red
A5=6 B5=Blue

I know if I have SUBTOTAL(4,A1:A5) the result will be 7, but how can I
specify I only want the SUBTOTAL function to consider items in the range
that match a corresponding value in the B column. For example if I have
the
same function, SUBTOTAL(4,A1:A5) but I want it only to look at
corresponding
Red values such that the result should be 5 as it would only be looking
at
A1, A3 & A4. This would permit me to find the highest value in the range
of
A1:A5 that were of Red.

--

Dave Peterson

http://www.veritasinsurance.com/sample.xls

"M.Siler" wrote in message
m...
A B C
1 Count Team Month
2 5 Red January
3 7 Blue January
4 12 TOTAL
5 3 Red February
6 4 Red February
7 6 Blue February
8 13 TOTAL
9
10 #VALUE! Max for RED
11 #VALUE! Max for BLUE

Where I defined a name range for the Teams as TeamRange =B2:B3,B5:B7
Where I defined a name range for the Count as CountRange =A2:A3,A5:A7

I tried the formula in A10 as =MAX(IF(TeamRange="Red",CountRange))
I tried the formula in A11 as =MAX(IF(TeamRange="Blue",CountRange))
I entered the formula as an array with Ctrl+Shift+Enter but I get #VALUE!
as
it doesn't like the fact the range is not contiguous.

To answer the question about using SUBTOTAL and MAX was because I have
turned on DataFilterAutoFilter and I want the values calculated by the
formula to only calculate on the visible items.

It might be easier if I was to post the workbook on my website... I'll
include the link in the next post -- in about 15 minutes from this one.

Thanks!


"Dave Peterson" wrote in message
...
Is there a reason you didn't use the simpler:
=max(a1:a5)

If there is no reason, maybe you could just use:
=MAX(IF(B1:B5="red",A1:A5))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

You may want to consider using a pivottable.

You'll be able find all the maximums without writing individual formulas.

"M.Siler" wrote:

A1=5 B1=Red
A2=7 B2=Blue
A3=2 B3=Red
A4=4 B4=Red
A5=6 B5=Blue

I know if I have SUBTOTAL(4,A1:A5) the result will be 7, but how can I
specify I only want the SUBTOTAL function to consider items in the range
that match a corresponding value in the B column. For example if I have
the
same function, SUBTOTAL(4,A1:A5) but I want it only to look at
corresponding
Red values such that the result should be 5 as it would only be looking
at
A1, A3 & A4. This would permit me to find the highest value in the
range
of
A1:A5 that were of Red.

--

Dave Peterson

Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$2 6)-MIN(ROW($C$9:$C$26))
,0,1))*($C$9:$C$26=B1),$D$9:$D$26))

....confirmed with CONTROL+SHIFT+ENTER. Then custom format D1:D3 as
follows...

Format Cells Number Custom Type: [=0]"--";General

Hope this helps!

In article ,
"M.Siler" wrote:

http://www.veritasinsurance.com/sample.xls

"M.Siler" wrote in message
m...
A B C
1 Count Team Month
2 5 Red January
3 7 Blue January
4 12 TOTAL
5 3 Red February
6 4 Red February
7 6 Blue February
8 13 TOTAL
9
10 #VALUE! Max for RED
11 #VALUE! Max for BLUE

Where I defined a name range for the Teams as TeamRange =B2:B3,B5:B7
Where I defined a name range for the Count as CountRange =A2:A3,A5:A7

I tried the formula in A10 as =MAX(IF(TeamRange="Red",CountRange))
I tried the formula in A11 as =MAX(IF(TeamRange="Blue",CountRange))
I entered the formula as an array with Ctrl+Shift+Enter but I get #VALUE!
as
it doesn't like the fact the range is not contiguous.

To answer the question about using SUBTOTAL and MAX was because I have
turned on DataFilterAutoFilter and I want the values calculated by the
formula to only calculate on the visible items.

It might be easier if I was to post the workbook on my website... I'll
include the link in the next post -- in about 15 minutes from this one.

Thanks!


"Dave Peterson" wrote in message
...
Is there a reason you didn't use the simpler:
=max(a1:a5)

If there is no reason, maybe you could just use:
=MAX(IF(B1:B5="red",A1:A5))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

You may want to consider using a pivottable.

You'll be able find all the maximums without writing individual formulas.

"M.Siler" wrote:

A1=5 B1=Red
A2=7 B2=Blue
A3=2 B3=Red
A4=4 B4=Red
A5=6 B5=Blue

I know if I have SUBTOTAL(4,A1:A5) the result will be 7, but how can I
specify I only want the SUBTOTAL function to consider items in the range
that match a corresponding value in the B column. For example if I have
the
same function, SUBTOTAL(4,A1:A5) but I want it only to look at
corresponding
Red values such that the result should be 5 as it would only be looking
at
A1, A3 & A4. This would permit me to find the highest value in the
range
of
A1:A5 that were of Red.

--

Dave Peterson




begin 666 sample.xls
[Image]

end

Domenic, Very Nicely Done. I'll break apart the formula and learn from it.
Thank you again!

"Domenic" wrote in message
...
Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$2 6)-MIN(ROW($C$9:$C$26))
,0,1))*($C$9:$C$26=B1),$D$9:$D$26))

...confirmed with CONTROL+SHIFT+ENTER. Then custom format D1:D3 as
follows...

Format Cells Number Custom Type: [=0]"--";General

Hope this helps!

In article ,
"M.Siler" wrote:

http://www.veritasinsurance.com/sample.xls

"M.Siler" wrote in message
m...
A B C
1 Count Team Month
2 5 Red January
3 7 Blue January
4 12 TOTAL
5 3 Red February
6 4 Red February
7 6 Blue February
8 13 TOTAL
9
10 #VALUE! Max for RED
11 #VALUE! Max for BLUE

Where I defined a name range for the Teams as TeamRange =B2:B3,B5:B7
Where I defined a name range for the Count as CountRange =A2:A3,A5:A7

I tried the formula in A10 as =MAX(IF(TeamRange="Red",CountRange))
I tried the formula in A11 as =MAX(IF(TeamRange="Blue",CountRange))
I entered the formula as an array with Ctrl+Shift+Enter but I get
#VALUE!
as
it doesn't like the fact the range is not contiguous.

To answer the question about using SUBTOTAL and MAX was because I have
turned on DataFilterAutoFilter and I want the values calculated by
the
formula to only calculate on the visible items.

It might be easier if I was to post the workbook on my website... I'll
include the link in the next post -- in about 15 minutes from this one.

Thanks!


"Dave Peterson" wrote in message
...
Is there a reason you didn't use the simpler:
=max(a1:a5)

If there is no reason, maybe you could just use:
=MAX(IF(B1:B5="red",A1:A5))
This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

You may want to consider using a pivottable.

You'll be able find all the maximums without writing individual
formulas.

"M.Siler" wrote:

A1=5 B1=Red
A2=7 B2=Blue
A3=2 B3=Red
A4=4 B4=Red
A5=6 B5=Blue

I know if I have SUBTOTAL(4,A1:A5) the result will be 7, but how can
I
specify I only want the SUBTOTAL function to consider items in the
range
that match a corresponding value in the B column. For example if I
have
the
same function, SUBTOTAL(4,A1:A5) but I want it only to look at
corresponding
Red values such that the result should be 5 as it would only be
looking
at
A1, A3 & A4. This would permit me to find the highest value in the
range
of
A1:A5 that were of Red.

--

Dave Peterson




begin 666 sample.xls
[Image]

end

Ok, I've tried, but I don't understand everything that is going on here.

Why ROW($C$9:$C$26)-MIN(ROW($C$9:$C$26)
Isn't that alway going to be zero?

"M.Siler" wrote in message
...
Domenic, Very Nicely Done. I'll break apart the formula and learn from
it. Thank you again!

"Domenic" wrote in message
...
Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$2 6)-MIN(ROW($C$9:$C$26))
,0,1))*($C$9:$C$26=B1),$D$9:$D$26))

...confirmed with CONTROL+SHIFT+ENTER. Then custom format D1:D3 as
follows...

Format Cells Number Custom Type: [=0]"--";General

Hope this helps!

In article ,
"M.Siler" wrote:

http://www.veritasinsurance.com/sample.xls

"M.Siler" wrote in message
m...
A B C
1 Count Team Month
2 5 Red January
3 7 Blue January
4 12 TOTAL
5 3 Red February
6 4 Red February
7 6 Blue February
8 13 TOTAL
9
10 #VALUE! Max for RED
11 #VALUE! Max for BLUE

Where I defined a name range for the Teams as TeamRange =B2:B3,B5:B7
Where I defined a name range for the Count as CountRange
=A2:A3,A5:A7

I tried the formula in A10 as =MAX(IF(TeamRange="Red",CountRange))
I tried the formula in A11 as =MAX(IF(TeamRange="Blue",CountRange))
I entered the formula as an array with Ctrl+Shift+Enter but I get
#VALUE!
as
it doesn't like the fact the range is not contiguous.

To answer the question about using SUBTOTAL and MAX was because I have
turned on DataFilterAutoFilter and I want the values calculated by
the
formula to only calculate on the visible items.

It might be easier if I was to post the workbook on my website... I'll
include the link in the next post -- in about 15 minutes from this
one.

Thanks!


"Dave Peterson" wrote in message
...
Is there a reason you didn't use the simpler:
=max(a1:a5)

If there is no reason, maybe you could just use:
=MAX(IF(B1:B5="red",A1:A5))
This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

You may want to consider using a pivottable.

You'll be able find all the maximums without writing individual
formulas.

"M.Siler" wrote:

A1=5 B1=Red
A2=7 B2=Blue
A3=2 B3=Red
A4=4 B4=Red
A5=6 B5=Blue

I know if I have SUBTOTAL(4,A1:A5) the result will be 7, but how can
I
specify I only want the SUBTOTAL function to consider items in the
range
that match a corresponding value in the B column. For example if I
have
the
same function, SUBTOTAL(4,A1:A5) but I want it only to look at
corresponding
Red values such that the result should be 5 as it would only be
looking
at
A1, A3 & A4. This would permit me to find the highest value in the
range
of
A1:A5 that were of Red.

--

Dave Peterson




begin 666 sample.xls
[Image]

end

Why couldn't I just use

=MAX(IF(($C$9:$C$26=B2),$D$9:$D$26))

Entered as an array (Ctrl+Shift+Enter)

"M.Siler" wrote in message
...
Ok, I've tried, but I don't understand everything that is going on here.

Why ROW($C$9:$C$26)-MIN(ROW($C$9:$C$26)
Isn't that alway going to be zero?

"M.Siler" wrote in message
...
Domenic, Very Nicely Done. I'll break apart the formula and learn from
it. Thank you again!

"Domenic" wrote in message
...
Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$2 6)-MIN(ROW($C$9:$C$26))
,0,1))*($C$9:$C$26=B1),$D$9:$D$26))

...confirmed with CONTROL+SHIFT+ENTER. Then custom format D1:D3 as
follows...

Format Cells Number Custom Type: [=0]"--";General

Hope this helps!

In article ,
"M.Siler" wrote:

http://www.veritasinsurance.com/sample.xls

"M.Siler" wrote in message
m...
A B C
1 Count Team Month
2 5 Red January
3 7 Blue January
4 12 TOTAL
5 3 Red February
6 4 Red February
7 6 Blue February
8 13 TOTAL
9
10 #VALUE! Max for RED
11 #VALUE! Max for BLUE

Where I defined a name range for the Teams as TeamRange
=B2:B3,B5:B7
Where I defined a name range for the Count as CountRange =A2:A3,A5:A7

I tried the formula in A10 as =MAX(IF(TeamRange="Red",CountRange))
I tried the formula in A11 as =MAX(IF(TeamRange="Blue",CountRange))
I entered the formula as an array with Ctrl+Shift+Enter but I get
#VALUE!
as
it doesn't like the fact the range is not contiguous.

To answer the question about using SUBTOTAL and MAX was because I
have
turned on DataFilterAutoFilter and I want the values calculated by
the
formula to only calculate on the visible items.

It might be easier if I was to post the workbook on my website...
I'll
include the link in the next post -- in about 15 minutes from this
one.

Thanks!


"Dave Peterson" wrote in message
...
Is there a reason you didn't use the simpler:
=max(a1:a5)

If there is no reason, maybe you could just use:
=MAX(IF(B1:B5="red",A1:A5))
This is an array formula. Hit ctrl-shift-enter instead of enter.
If you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

You may want to consider using a pivottable.

You'll be able find all the maximums without writing individual
formulas.

"M.Siler" wrote:

A1=5 B1=Red
A2=7 B2=Blue
A3=2 B3=Red
A4=4 B4=Red
A5=6 B5=Blue

I know if I have SUBTOTAL(4,A1:A5) the result will be 7, but how
can I
specify I only want the SUBTOTAL function to consider items in the
range
that match a corresponding value in the B column. For example if I
have
the
same function, SUBTOTAL(4,A1:A5) but I want it only to look at
corresponding
Red values such that the result should be 5 as it would only be
looking
at
A1, A3 & A4. This would permit me to find the highest value in the
range
of
A1:A5 that were of Red.

--

Dave Peterson




begin 666 sample.xls
[Image]

end

Ok, this only works when I haven't used the autofilter. It's clear I don't
understand. Can someone explain how Domenic's solution works??

"M.Siler" wrote in message
...
Why couldn't I just use

=MAX(IF(($C$9:$C$26=B2),$D$9:$D$26))

Entered as an array (Ctrl+Shift+Enter)

"M.Siler" wrote in message
...
Ok, I've tried, but I don't understand everything that is going on here.

Why ROW($C$9:$C$26)-MIN(ROW($C$9:$C$26)
Isn't that alway going to be zero?

"M.Siler" wrote in message
...
Domenic, Very Nicely Done. I'll break apart the formula and learn from
it. Thank you again!

"Domenic" wrote in message
...
Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$2 6)-MIN(ROW($C$9:$C$26))
,0,1))*($C$9:$C$26=B1),$D$9:$D$26))

...confirmed with CONTROL+SHIFT+ENTER. Then custom format D1:D3 as
follows...

Format Cells Number Custom Type: [=0]"--";General

Hope this helps!

In article ,
"M.Siler" wrote:

http://www.veritasinsurance.com/sample.xls

"M.Siler" wrote in message
m...
A B C
1 Count Team Month
2 5 Red January
3 7 Blue January
4 12 TOTAL
5 3 Red February
6 4 Red February
7 6 Blue February
8 13 TOTAL
9
10 #VALUE! Max for RED
11 #VALUE! Max for BLUE

Where I defined a name range for the Teams as TeamRange =B2:B3,B5:B7
Where I defined a name range for the Count as CountRange
=A2:A3,A5:A7

I tried the formula in A10 as =MAX(IF(TeamRange="Red",CountRange))
I tried the formula in A11 as =MAX(IF(TeamRange="Blue",CountRange))
I entered the formula as an array with Ctrl+Shift+Enter but I get
#VALUE!
as
it doesn't like the fact the range is not contiguous.

To answer the question about using SUBTOTAL and MAX was because I
have
turned on DataFilterAutoFilter and I want the values calculated by
the
formula to only calculate on the visible items.

It might be easier if I was to post the workbook on my website...
I'll
include the link in the next post -- in about 15 minutes from this
one.

Thanks!


"Dave Peterson" wrote in message
...
Is there a reason you didn't use the simpler:
=max(a1:a5)

If there is no reason, maybe you could just use:
=MAX(IF(B1:B5="red",A1:A5))
This is an array formula. Hit ctrl-shift-enter instead of enter.
If you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

You may want to consider using a pivottable.

You'll be able find all the maximums without writing individual
formulas.

"M.Siler" wrote:

A1=5 B1=Red
A2=7 B2=Blue
A3=2 B3=Red
A4=4 B4=Red
A5=6 B5=Blue

I know if I have SUBTOTAL(4,A1:A5) the result will be 7, but how
can I
specify I only want the SUBTOTAL function to consider items in the
range
that match a corresponding value in the B column. For example if I
have
the
same function, SUBTOTAL(4,A1:A5) but I want it only to look at
corresponding
Red values such that the result should be 5 as it would only be
looking
at
A1, A3 & A4. This would permit me to find the highest value in
the
range
of
A1:A5 that were of Red.

--

Dave Peterson




begin 666 sample.xls
[Image]

end

In article ,
"M.Siler" wrote:

Ok, I've tried, but I don't understand everything that is going on here.

Why ROW($C$9:$C$26)-MIN(ROW($C$9:$C$26)
Isn't that alway going to be zero?

ROW($C$9:$C$26) returns the following array of values...

9
10
11
12
..
..
..
26

MIN(ROW($C$9:$C$26)) returns a single value, that being 9

So if we have...

ROW($C$9:$C$26)-MIN(ROW($C$9:$C$26)

....the following array of values is returned...

9 - 9 --- 0
10 - 9 --- 1
11 - 9 --- 2
12 - 9 --- 3
..
..
..
26 - 9 --- 17

This array of values is used for the second argument of the OFFSET
function. In effect, we get an array of references...

OFFSET($C$9:$C$26,0,0,1)) --- C9
OFFSET($C$9:$C$26,1,0,1)) --- C10
OFFSET($C$9:$C$26,2,0,1)) --- C11
OFFSET($C$9:$C$26,3,0,1)) --- C12
..
..
..
OFFSET($C$9:$C$26,17,0,1)) --- C26

SUBTOTAL(3,OFFSET(...)) returns an array of 1's and 0's. The SUBTOTAL
function evaluates each reference provided by OFFSET. If the cell is
not empty, it returns a 1. If the cell is empty, or if the cell is not
visible when data is filtered, it returns a 0. Note that SUBTOTAL's
first argument is set to 3, which invokes the COUNTA function.

Hope this helps!

Thanks for the additional help... I'll see if I can follow it this time.
Wish me luck.

"Domenic" wrote in message
...
In article ,
"M.Siler" wrote:

Ok, I've tried, but I don't understand everything that is going on here.

Why ROW($C$9:$C$26)-MIN(ROW($C$9:$C$26)
Isn't that alway going to be zero?

ROW($C$9:$C$26) returns the following array of values...

9
10
11
12
.
.
.
26

MIN(ROW($C$9:$C$26)) returns a single value, that being 9

So if we have...

ROW($C$9:$C$26)-MIN(ROW($C$9:$C$26)

...the following array of values is returned...

9 - 9 --- 0
10 - 9 --- 1
11 - 9 --- 2
12 - 9 --- 3
.
.
.
26 - 9 --- 17

This array of values is used for the second argument of the OFFSET
function. In effect, we get an array of references...

OFFSET($C$9:$C$26,0,0,1)) --- C9
OFFSET($C$9:$C$26,1,0,1)) --- C10
OFFSET($C$9:$C$26,2,0,1)) --- C11
OFFSET($C$9:$C$26,3,0,1)) --- C12
.
.
.
OFFSET($C$9:$C$26,17,0,1)) --- C26

SUBTOTAL(3,OFFSET(...)) returns an array of 1's and 0's. The SUBTOTAL
function evaluates each reference provided by OFFSET. If the cell is
not empty, it returns a 1. If the cell is empty, or if the cell is not
visible when data is filtered, it returns a 0. Note that SUBTOTAL's
first argument is set to 3, which invokes the COUNTA function.

Hope this helps!

That's very cool. I'm going to have to play around with this more, but wow,
nice thought process to come up with that. Thank you again and I hope this
helps others as well.

"M.Siler" wrote in message
m...
Thanks for the additional help... I'll see if I can follow it this time.
Wish me luck.

"Domenic" wrote in message
...
In article ,
"M.Siler" wrote:

Ok, I've tried, but I don't understand everything that is going on here.

Why ROW($C$9:$C$26)-MIN(ROW($C$9:$C$26)
Isn't that alway going to be zero?

ROW($C$9:$C$26) returns the following array of values...

9
10
11
12
.
.
.
26

MIN(ROW($C$9:$C$26)) returns a single value, that being 9

So if we have...

ROW($C$9:$C$26)-MIN(ROW($C$9:$C$26)

...the following array of values is returned...

9 - 9 --- 0
10 - 9 --- 1
11 - 9 --- 2
12 - 9 --- 3
.
.
.
26 - 9 --- 17

This array of values is used for the second argument of the OFFSET
function. In effect, we get an array of references...

OFFSET($C$9:$C$26,0,0,1)) --- C9
OFFSET($C$9:$C$26,1,0,1)) --- C10
OFFSET($C$9:$C$26,2,0,1)) --- C11
OFFSET($C$9:$C$26,3,0,1)) --- C12
.
.
.
OFFSET($C$9:$C$26,17,0,1)) --- C26

SUBTOTAL(3,OFFSET(...)) returns an array of 1's and 0's. The SUBTOTAL
function evaluates each reference provided by OFFSET. If the cell is
not empty, it returns a 1. If the cell is empty, or if the cell is not
visible when data is filtered, it returns a 0. Note that SUBTOTAL's
first argument is set to 3, which invokes the COUNTA function.

Hope this helps!

In article ,
"M.Siler" wrote:

That's very cool. I'm going to have to play around with this more, but wow,
nice thought process to come up with that. Thank you again and I hope this
helps others as well.

You're very welcome! Glad I could help!

By the way, if I'm not mistaken, I believe this idiom comes courtesy of
Laurent Longre. Ingenious!