Conditional SUBTOTAL

  • Thread starter Thread starter M.Siler
  • Start date Start date
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.
 
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.
 
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!
 
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...
 
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...
 
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...
 
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...
 
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...
 
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!
 
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 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!
 
Back
Top