|
Conditional SUBTOTAL
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! |
| All times are GMT +1. The time now is 08:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com