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