Excel - SUMIF for only the Visible Cells in Excel - Duel 187 - Episode 2164

Microsoft Excel Tutorial: SUMIFS for visible cells in Excel.

In this Dueling Excel Podcast, Bill Jelen and Mike Girvin take on the challenge of performing a SUMIF for only the visible rows in an Excel filtered data set. They explore different approaches to tackle this issue, from formulas to workarounds.

Bill initially attempts to use the AGGREGATE function to ignore hidden rows, but faces some challenges. He then turns it over to Mike, who comes up with a clever solution involving the OFFSET function and array logic, a trick he learned from the MrExcel Message Board.

Mike's workaround involves building the OFFSET function with the ROWS function inside, which forces the evaluation as an array. This results in Offset delivering precisely one cell for each item in the range. The OFFSET function is then passed to the SUBTOTAL function, making it work seamlessly. To wrap it all up, the SUMPRODUCT function is used to get the desired result.

Bill also offers an alternative formula solution, suggesting the use of a Helper column with the AGGREGATE function pointing to the current row. This approach generates a column that contains zeroes for hidden rows and actual values for visible rows. Using SUMIF on the Helper column allows for the calculation of the SUMIF of visible cells.

Join Bill and Mike as they navigate the challenges of Excel and come up with creative solutions for performing calculations on visible rows in filtered data. Thanks for tuning in, and don't forget to subscribe for more Dueling Excel Podcasts from MrExcel and Excel Is Fun!

Table of Contents
(00:00) Dueling Excel 187 SUMIF the Visible Rows in Excel
(00:32) SUMIF on a filtered data set
(01:01) AGGREGATE function can ignore hidden rows
(01:53) Multiply numbers by a logical test
(03:22) SUBTOTAL cannot handle an array operation there.
(03:50) Using OFFSET function
(04:48) Using ROW function inside of OFFSET function
(07:11) Wrapping in SUMPRODUCT
(08:33) Testing with Filters
(09:28) OFFSET disguises the array!
(10:07) Helper Column to Detect if Current Row is hidden
(10:30) AGGREGATE of only current row
(11:42) Episode Recap
(12:03) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelduel #excelchallenge #excelfilter #exceldatafiltering

This video answers these common search terms:
how do you sum only visible cells in excel
how do you sumif only visible cells in excel
using sumif with filtered data
excel sumif visible cells
how to sumif filtered data in excel
sumif function with filtered rows
calculating sumif for visible rows only
using sumif with autofilter in excel
sumif for filtered data in excel
excel sumif excluding hidden rows
filtered range sumif formula
sum visible rows in excel using sumif
conditional sum for visible cells in excel

Question from Jon: Do a SUMIFS that only adds the visible cells
Bill's first try: Pass an array into the AGGREGATE function - but this fails
Mike's awesome solution:
SUBTOTAL or AGGREGATE can not accept an array
But you can use OFFSET to process an array and send the results to SUBTOTAL.
Use SUMPRODUCT to figure out if the row is YES or not.
Bill's second try is like checking to see if the refrigerator light goes out when the door closes:
Add a helper column with AGGREGATE of a single cell in each row
Point the SUMIFS at that column

[jagadeeshkumar]

That was an awesome solution with a step-by-step answer. Thanks a lot for taking the time to create this video

[edgecondition]

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.

[ismailismaili]

Mr. Bill and Mr. Mike you are absolutely amazing people

[samirkapadia]

This trick was really helpful. Thanks Mike & Bill.
Get to learn something new from you guys every day

[krn]

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.

[bricc]

I have so much fun watching your battles. Thank you so much for all that you share and teach.

[brunofeb]

Dammn, these guys are monsters. Very clear video ! It worked here for me

[semperdiscendum]

That was Awesome! Thank you for teaching us more good stuff ! 👍👍

[abookiabel]

Followed thru clearly and worked out my challenge. Thx. Lookout for more solutions

[Backtoflying]

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!

[Sal_A]

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.

[DangosAreCute]

AMAZING!! Thanks so much for the help!!!

[anthonycastagnola]

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.

[The_Code_Father]

Awesome Video i've been searching for this but why should i use helper column if i intend to go with AGREGATE ??

[eisaal-binali]

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

[davidalvarado]

Is there a way to do it using wildcards as conditionals?

[elianeliria]

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)")) ????

[tirumalagirisreepati]

how to sum only visisble columns sir -i mean if I hide any column it should reflect in total-please clarify

[DavidB-dpwe]

why isn't this just =SUMIFS(B6:B20, A6:A20, A5, C6:C20, C6)