MrExcel Publishing
Your One Stop for Excel Tips & Solutions

SUMIF Visible Cells


October 27, 2017 - by Bill Jelen

SUMIF Visible Cells

How to use a SUMIF to sum the *visible* cells that match a criteria. For example, sum all of the records where the Region="East", but ignore the rows that are already hidden. You will be surprised at the function that saves the day.


Watch Video

  • 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

Auto-Generated Transcript

  • hey welcome back it's time for another
  • dueling Excel podcast I'm Bill Jelen
  • from MrExcel be joined by Mike Irvin
  • from Excel is fun this is our episode
  • 187 some if the visible rose alright
  • welcome back I was doing a seminar in
  • Fort Myers Florida yesterday and John
  • was in the audience and he had a
  • question he said how can you do a sum if
  • it only sums the visible rows okay so
  • right here's our database and some if
  • it's simple enough
  • look 385 to 824 see if the answer is yes
  • if it is add up the corresponding cells
  • from b5 to be 24 that all works but then
  • John was applying a filter so we had a
  • category over here and data let's just
  • use the auto filter to quickly apply the
  • filter so there are all of our B cells
  • and we want to add up just the yes cells
  • that are B cells then that should be
  • something to 100 but this formula is not
  • working so the question is how can we do
  • a sum if that only looks at the visible
  • rows and I said all right well clearly
  • the only thing I know that's going to
  • ignore visible rows is the aggregate
  • function all right the aggregate
  • function so we're gonna some number 9
  • comma and then here's the important part
  • ignore the hidden rows all right and
  • then I just need to find some way to get
  • an array in there and I know the higher
  • versions use an array like everything
  • above 11 for that second argument use an
  • array but you know will an array work
  • here all right so what I wanted to said
  • I was going to do I was gonna say all
  • right we're gonna take all these numbers
  • here and multiply them times a boolean
  • so look through all of the words over an
  • a 5 equal to yes all right and what's
  • gonna happen is well these are going to
  • be numbers right and these are going to
  • evaluate in a series of trues and falses
  • and then when we force Excel to multiply
  • the trues and falses times a number the
  • throughs become 1
  • all right so anything it doesn't say yes
  • is going to evaluate to 0 anything that
  • does that yes is going to evaluate to
  • the sales all right and then we just
  • have to do a ctrl shift enter
  • so my god it doesn't work I don't maybe
  • just an enter tell you what Mike I'm
  • gonna keep working on this i'ma throw it
  • for you
  • let's see what you have thanks MrExcel
  • well you know what aggregate yes that's
  • one of two functions that can ignore
  • hidden or filtered rows now aggregate
  • was invented in Excel 2010 before
  • aggregate we had the subtotal function
  • and the subtotal function can also
  • ignore hidden rows or filtered rows now
  • the same problem occurs with subtotal as
  • with aggregate if I choose function 9
  • that we'll just avoid counting filtered
  • rows if I choose 109 that will ignore
  • hidden and filtered rows so 109 I would
  • like to do that but guess what this
  • subtotal function runs into the same
  • problem as the aggregate reference
  • argument means you cannot have an array
  • operation there so what do we do we're
  • gonna use the offset function to
  • simultaneously get each individual row
  • which subtotal will in essence create
  • individual sub tools and this trick I
  • learned years ago from MrExcels very
  • Oh MrExcel message board now offset it
  • needs a starting point so I'm going to
  • click in the first cell comma and then
  • offset goes down or up a certain number
  • of rows to get a particular value I'm
  • going to say just as an example to see
  • how this function works go down six rows
  • comma columns that's how many rows you
  • want to subtract our ad we don't want to
  • subtract or add any so I'm going to
  • leave it blank
  • now offset notice I already have some
  • rows hid in there three rows so offset
  • if I tell it to go down six out of a one
  • two three four
  • five and six so offset should be going
  • and getting that 30 in Selby 16 if I
  • highlight it and hit the f9 key I can
  • see sure enough that it's doing exactly
  • that
  • control-z but what I really want it to
  • do is simultaneously get every single
  • value so in rows amuse the row function
  • highlight all the way from ten to twenty
  • nine close parenthesis if I highlight
  • this row which is now doing a function
  • argument array operation will just
  • deliver an array of the numbers ten to
  • twenty nine so f9 there we go ah but we
  • really want to tell offset to go down
  • zero one two so it can get offset zero
  • offset one and so on if I can subtract
  • 10 from all of these I'll have exactly
  • what I want
  • zero one two three and so on control-z
  • well now I simply minus the row of the
  • very first cell in that range close
  • parenthesis now in the rows argument if
  • I f9 there's an array of all the
  • positions offset needs to offset to get
  • each item individually
  • control-z that is an array in row so it
  • will force offset to deliver exactly one
  • cell for each item in that range and
  • that'll be different than just the
  • complete range which subtotal cannot
  • handle comma columns we don't need any
  • so close parenthesis that hole offset if
  • I f9 look at that right now it's
  • delivering every single cell including
  • right there is three items that are
  • hidden I can see them 30 40 30 but
  • that's because I haven't dumped this
  • entire resultant array into subtotal
  • control Z I come to the end closed
  • parenthesis and now when I am nine watch
  • this there we are 0 0 z
  • because subtotal is ignoring the hidden
  • or later we'll see that it will ignore
  • filtered rows also now I would like to
  • further eliminate some of these numbers
  • for example the second number 50 we
  • can't have that because it says no right
  • there so I'm going to put this result in
  • array inside of some product and then
  • multiply this array times another array
  • of zeros and ones that will represent
  • yes that'll be the one no that'll be the
  • zero
  • control-z now I put it inside of some
  • product that's array one I need to come
  • to be in comma and highlight the column
  • with the yeses equals and I have the
  • criteria up here now if I click on array
  • 2 + f9 trues and falses but some product
  • can see those trues and falses so we
  • need to convert them to ones and zeros
  • ctrl z so I'm going to use any math
  • operation to convert trues and falses to
  • ones and zeros I'm gonna use double
  • negative because in general that tends
  • to be the fastest and most efficient now
  • I could have taken that array and
  • directly multiplied it by the first
  • array then we would just have that
  • multiplication in array 1 but I've
  • chosen to have array number 1 there then
  • some product will multiply times these
  • ones and zeros f9 and we'll get exactly
  • what we want notice right now 1 1 1
  • means yes yes yes but those will be
  • matched up with the zero zero zero from
  • the subtotal for the hidden rows
  • control-z now I can come to the end
  • close parentheses control enter now I'm
  • hiding over here if I come over and hide
  • right-click hi I should get exactly a
  • total of 50 and 10 control-z now if I
  • turn on the filters with ctrl shift L
  • now I can come up and filter just to
  • show B and there we go
  • 30 times 3 is 90 plus 10 is 100 so there
  • after a great trick I learned years ago
  • from the MrExcel message board
  • subtotal with offset to get our some ifs
  • which is really some product with a
  • criteria here to add only the filtered
  • rows and one last thing about offset
  • remember we were talking about aggregate
  • and subtotal couldn't handle an array
  • operation right there but we do have
  • some array operations inside of offset
  • offset delivers something that Excel can
  • interpret as a reference it works to
  • disguise the array operation as a
  • reference that in our case subtotal can
  • understand all right I'm gonna throw it
  • back over to MrExcel Oh Mike that is
  • one awesome formula I wonder I couldn't
  • come up with it there alive at the
  • seminar I was headed in completely the
  • wrong direction but well you've been
  • working on that I'm still absolutely
  • convinced that aggregate there's some
  • way that I can use aggregate to solve
  • this but if I can't use an array here
  • because this is five and not one of the
  • ones higher than twelve then I still
  • feel like there should be some way to do
  • this and check this out here's what I'm
  • gonna do is I'm gonna use a helper
  • column and you know as I started think
  • about this one this is this is a lot
  • like finding out if the refrigerator
  • light turns off when you close the door
  • all right you're not going to know but
  • what I'm gonna do is I'm gonna add it
  • and help her column with an aggregate
  • function and that aggregate function is
  • going to sum ignore the hidden rows and
  • what am I gonna sum I'm gonna sum just
  • this row just this one row and we will
  • copy that down all right now the big
  • question is what is the answer to this
  • when I filter to just the B's what is
  • the answer
  • are those hidden rows returning zero or
  • not well I don't know how we can find
  • out well actually I do know how we can
  • find out because we have a secret way to
  • look inside that sound all right so
  • here's what I'm doing I'm a press
  • control ditto to bring that exact
  • formula down and then instead of
  • pointing to column B I want to point at
  • column C like that
  • right and we'll press ENTER and then
  • apply a filter all right and let's see
  • 30 plus 30 plus 30 plus 10 100 it's
  • working so apparently in those hidden
  • rows the rows we can't see like for
  • example row 7 here we could do down here
  • equals C 7 aggregate is returning 0
  • because it that hidden row that row
  • itself is hidden all right there you go
  • kind of cheating with that helper column
  • but at least it's something that I can
  • understand and probably even knock out
  • again without having to look at the
  • formula all right wrap up from today
  • question from John do a summits that
  • only has the visible cells my first pass
  • I was trying to pass an array into the
  • aggregate function but that fails and
  • Mike came up with an awesome solution
  • use the offset function to point each
  • row use the subtotal to see that rows
  • hidden or not and then some product to
  • multiply you know is it yes times the
  • result from the subtotal my second try
  • it's like checking to see if the
  • refrigerator like goes out once the door
  • closes I had to help her column to each
  • row that says an aggregate of the cell
  • in this row saying ignore any hidden row
  • so that'll change to 0 on the rock is
  • hidden and then point the sum if that
  • column well there you have it thanks for
  • stopping by we'll see next time for
  • another dueling excel podcast from mr.
  • Excel and Excel is fun

Download File

Download the sample file here: Duel187.xlsx

Title Photo: ugglemamma / pixabay


Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.