Conditional SUBTOTAL

M

M.Siler

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.
 
D

Dave Peterson

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

M.Siler

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 Data>Filter>AutoFilter 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!
 
D

Domenic

Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$26)-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!

M.Siler said:
http://www.veritasinsurance.com/sample.xls

M.Siler said:
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 Data>Filter>AutoFilter 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 said:
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.

:

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.
Click to expand...


begin 666 sample.xls
[Image]

end
Click to expand...
Click to expand...
 
M

M.Siler

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

Domenic said:
Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$26)-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!

M.Siler said:
http://www.veritasinsurance.com/sample.xls

M.Siler said:
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 Data>Filter>AutoFilter 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!


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.

:

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.
Click to expand...


begin 666 sample.xls
[Image]

end
Click to expand...
Click to expand...
 
M

M.Siler

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 said:
Domenic, Very Nicely Done. I'll break apart the formula and learn from
it. Thank you again!

Domenic said:
Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$26)-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!

M.Siler said:
http://www.veritasinsurance.com/sample.xls

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 Data>Filter>AutoFilter 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!


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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
M

M.Siler

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 said:
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 said:
Domenic, Very Nicely Done. I'll break apart the formula and learn from
it. Thank you again!

Domenic said:
Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$26)-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!

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

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 Data>Filter>AutoFilter 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!


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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
M

M.Siler

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 said:
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 said:
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 said:
Domenic, Very Nicely Done. I'll break apart the formula and learn from
it. Thank you again!

Try...

D1, copied down:

=MAX(IF(SUBTOTAL(3,OFFSET($C$9:$C$26,ROW($C$9:$C$26)-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!

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

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 Data>Filter>AutoFilter 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!


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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
D

Domenic

M.Siler said:
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?
Click to expand...

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!
 
M

M.Siler

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.
 
D

Domenic

M.Siler said:
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.
Click to expand...

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!
 
Top