Thanks, Mike! I’d seen this kind of solution in forums, but I had a real mental block in understanding how it works. That degree in nucular fizicks wasn’t quite as useful as I’d hoped. I hesitate to put stuff in spreadsheets that I don’t understand, but I think that I have finally grokked this one thanks to you.
Great job guys. Mike gets point this time for that crazy sumproduct subtotal offset formula. Bill also gets credit though for creating the mrexcel message board. Great resource for all Excel questions, formulas or VBA related.
Thank you so much for posting this! The first time I used the AGGREGATE and helper column method I forgot to reference the helper column (I just referenced the original figures) and I couldn't figure out why the numbers wouldn't change when I filtered LOL. So I used =AGGREGATE(9,5,C36) since my data is in column C then filled down and I used =SUMIF($G$36:$G$1179,B25,$A$36:$A$1179). Those two ranges were: the column of words to filter by; then the B25 is the SUMIF criteria, and then the final range is those helper values we just filled down. I made them absolute by pressing F4 so that I could fill this down: I have several rows of criterion I wanted to SUMIF by. I used this to filter my mileage log from Everlance.com: I used Excel's autofilter to filter to one specific vehicle, and these formulas to add up miles (kilometres) for work, personal, medical, etc. Thanks again!
Slick solutions! Microsoft should of been able handle hidden & filters situations a lot easier given that AGGREGATE and SUBTOTAL functions were designed to ignore hidden rows/filters.
At the 10:10 mark regarding the "Helper" column. The following is what I've used for a 24 month trial balance report. I came up with the formula around (if not before) 2011. In cell "A2" type only "Yes". In cell "B2" type: "=SUMIF($C$4:$C$24,"1"&A2,$B$4:$B$24)". In cell A3, type only "No". In cell B3 "=SUMIF($C$4:$C$24,"1"&A3,$B$4:$B$24)". In the "Helper" column in cell "C5" type "=SUBTOTAL(3,B5)&A5". The result should equal "1Yes" (or "1No"). Copy formula from C5 down to C24. Filter on column D "Cat" ="B", the data in C5 should change to "0Yes" (or "0No"). The formulas in B2 & B3 will be Summing if column C contains 1(and)Yes [1Yes] and 1(and)No [1No]. I use a variation of this formula on 195,000 rows and 24 months of data ranging from Petty Cash to Corp Income Taxes, to create a P&L report that when the data is filtered on one of the various business units in the organization, am able to view a quick P&L on each of the 24 months on that specific business unit.
I am trying to replicate this formula, but is not working for me. Am I doing something wrong: =SUMPRODUCT(SUBTOTAL(109,OFFSET(BS15,ROW(BS15:BS17719)-ROW(BS15),)),--(BN15:BN17719="41\CSM (Sheet Metal)")) ????
Thank you Bill and Mike for helping people all over the world. i am trying to use this technique to find the distinct values but it;s not working , this is my fourmual =SUM(1/COUNTIF(SUBTOTAL(103,OFFSET(E7,ROW(E7:E119)-ROW(E7),)),SUBTOTAL(103,OFFSET(E7,ROW(E7:E119)-ROW(E7),))) any help in this please
My friend Bob Umlas has been complaining for years that there is nothing to detect if a column is hidden. The SUBTOTAL and AGGREGATE functions can detect if a Row is hidden. But there is no function to tell if a column is hidden. The easiest solution might be to change your workbook so you can hide rows instead of columns. Otherwise, you will have to turn to VBA or Typescript macros to do these calculations.
That was an awesome solution with a step-by-step answer. Thanks a lot for taking the time to create this video
This trick was really helpful. Thanks Mike & Bill.
Get to learn something new from you guys every day
Thanks, Mike! I’d seen this kind of solution in forums, but I had a real mental block in understanding how it works. That degree in nucular fizicks wasn’t quite as useful as I’d hoped. I hesitate to put stuff in spreadsheets that I don’t understand, but I think that I have finally grokked this one thanks to you.
Followed thru clearly and worked out my challenge. Thx. Lookout for more solutions
Dammn, these guys are monsters. Very clear video ! It worked here for me
Great job guys. Mike gets point this time for that crazy sumproduct subtotal offset formula. Bill also gets credit though for creating the mrexcel message board. Great resource for all Excel questions, formulas or VBA related.
Mr. Bill and Mr. Mike you are absolutely amazing people
That was Awesome! Thank you for teaching us more good stuff ! 👍👍
I have so much fun watching your battles. Thank you so much for all that you share and teach.
Thank you so much for posting this! The first time I used the AGGREGATE and helper column method I forgot to reference the helper column (I just referenced the original figures) and I couldn't figure out why the numbers wouldn't change when I filtered LOL. So I used =AGGREGATE(9,5,C36) since my data is in column C then filled down and I used =SUMIF($G$36:$G$1179,B25,$A$36:$A$1179). Those two ranges were: the column of words to filter by; then the B25 is the SUMIF criteria, and then the final range is those helper values we just filled down. I made them absolute by pressing F4 so that I could fill this down: I have several rows of criterion I wanted to SUMIF by. I used this to filter my mileage log from Everlance.com: I used Excel's autofilter to filter to one specific vehicle, and these formulas to add up miles (kilometres) for work, personal, medical, etc. Thanks again!
Slick solutions! Microsoft should of been able handle hidden & filters situations a lot easier given that AGGREGATE and SUBTOTAL functions were designed to ignore hidden rows/filters.
AMAZING!! Thanks so much for the help!!!
Thanks Bill & Mike!
Thankyou so much
Very Smart Mr Excel & ExcelIsFun
At the 10:10 mark regarding the "Helper" column.
The following is what I've used for a 24 month trial balance report. I came up with the formula around (if not before) 2011.
In cell "A2" type only "Yes".
In cell "B2" type: "=SUMIF($C$4:$C$24,"1"&A2,$B$4:$B$24)".
In cell A3, type only "No".
In cell B3 "=SUMIF($C$4:$C$24,"1"&A3,$B$4:$B$24)".
In the "Helper" column in cell "C5" type "=SUBTOTAL(3,B5)&A5". The result should equal "1Yes" (or "1No").
Copy formula from C5 down to C24.
Filter on column D "Cat" ="B", the data in C5 should change to "0Yes" (or "0No").
The formulas in B2 & B3 will be Summing if column C contains 1(and)Yes [1Yes] and 1(and)No [1No].
I use a variation of this formula on 195,000 rows and 24 months of data ranging from Petty Cash to Corp Income Taxes, to create a P&L report that when the data is filtered on one of the various business units in the organization, am able to view a quick P&L on each of the 24 months on that specific business unit.
Awesome Video i've been searching for this but why should i use helper column if i intend to go with AGREGATE ??
Useful Technique.
I am trying to replicate this formula, but is not working for me. Am I doing something wrong: =SUMPRODUCT(SUBTOTAL(109,OFFSET(BS15,ROW(BS15:BS17719)-ROW(BS15),)),--(BN15:BN17719="41\CSM (Sheet Metal)")) ????
Is there a way to do it using wildcards as conditionals?
Thank you Bill and Mike for helping people all over the world.
i am trying to use this technique to find the distinct values but it;s not working , this is my fourmual =SUM(1/COUNTIF(SUBTOTAL(103,OFFSET(E7,ROW(E7:E119)-ROW(E7),)),SUBTOTAL(103,OFFSET(E7,ROW(E7:E119)-ROW(E7),)))
any help in this please
how to sum only visisble columns sir -i mean if I hide any column it should reflect in total-please clarify
My friend Bob Umlas has been complaining for years that there is nothing to detect if a column is hidden.
The SUBTOTAL and AGGREGATE functions can detect if a Row is hidden. But there is no function to tell if a column is hidden.
The easiest solution might be to change your workbook so you can hide rows instead of columns.
Otherwise, you will have to turn to VBA or Typescript macros to do these calculations.
why isn't this just =SUMIFS(B6:B20,A6:A20,A5,C6:C20,C6)
SUMIFS includes hidden rows.