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
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
Transcript of the video:
Hey! Welcome back it's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel, we'll be joined by Mike Girvin from Excel Is Fun.
This is our episode 187, SUMIF the Visible Rows.
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 sumif? It only sums the visible rows." Okay! So, right here's our database and sumif 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 B24 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 sumif, 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.
Alright! The aggregate function.
So, we're gonna sum number 9 comma and then here's the important part ignore the hidden rows.
Alright!
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 A5.
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 trues 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 ].
Son of a gun, it doesn't work.
I don't know maybe just an [ enter ] Tell you what, Mike!
I'm gonna keep working on this.
I'm throw it for you, let's see what you have.
Mike: 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 MrExcel very own 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 or add.
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 cell B16.
If I highlight it and hit the [ F9 ] key.
I can see sure enough that it's doing exactly that.
[ ctrl + Z ] But what I really want it to do is simultaneously get every single value.
So, in rows I'm gonna use the row function.
Highlight all the way from 10 to 29, close parenthesis.
If I highlight this row which is now doing a function argument, array operation will just deliver an array of the numbers 10 to 29.
So, [ F9 ].
There we go.
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.
[ ctrl 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.
[ ctrl 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 whole 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 [ ctrl Z ].
I come to the end, close parenthesis and now when I [ F9 ], watch this.
There we are 0, 0, 0.
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 sum 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 sum product.
That's array one.
I need to come to the end comma and highlight the column with the 'yeses'.
Equals and I have the criteria up here.
Now, if I click on array 2 and [ F9 ], trues and falses.
But sum 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 sum 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.
[ ctrl Z ] Now, I can come to the end, close parentheses. [ ctrl enter ] Now, I'm hiding over here if I come over and hide, right-click hide.
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 [ F2 ] great trick, I learned years ago from the MrExcel Message Board.
Subtotal with offset to get our sumif, which is really sum 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.
MrExcel: Mike! that is one awesome formula.
I wonder, I couldn't come up with it there, live at the seminar.
I was headed in completely the wrong direction.
But when on 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 in helper 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.
Alright! 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 gonna 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 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 sumifs 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 sum 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 a helper 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 when the row is hidden and then point the sumif that column.
Well, there you have it.
Thanks for stopping by, we'll see next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel, we'll be joined by Mike Girvin from Excel Is Fun.
This is our episode 187, SUMIF the Visible Rows.
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 sumif? It only sums the visible rows." Okay! So, right here's our database and sumif 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 B24 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 sumif, 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.
Alright! The aggregate function.
So, we're gonna sum number 9 comma and then here's the important part ignore the hidden rows.
Alright!
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 A5.
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 trues 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 ].
Son of a gun, it doesn't work.
I don't know maybe just an [ enter ] Tell you what, Mike!
I'm gonna keep working on this.
I'm throw it for you, let's see what you have.
Mike: 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 MrExcel very own 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 or add.
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 cell B16.
If I highlight it and hit the [ F9 ] key.
I can see sure enough that it's doing exactly that.
[ ctrl + Z ] But what I really want it to do is simultaneously get every single value.
So, in rows I'm gonna use the row function.
Highlight all the way from 10 to 29, close parenthesis.
If I highlight this row which is now doing a function argument, array operation will just deliver an array of the numbers 10 to 29.
So, [ F9 ].
There we go.
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.
[ ctrl 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.
[ ctrl 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 whole 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 [ ctrl Z ].
I come to the end, close parenthesis and now when I [ F9 ], watch this.
There we are 0, 0, 0.
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 sum 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 sum product.
That's array one.
I need to come to the end comma and highlight the column with the 'yeses'.
Equals and I have the criteria up here.
Now, if I click on array 2 and [ F9 ], trues and falses.
But sum 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 sum 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.
[ ctrl Z ] Now, I can come to the end, close parentheses. [ ctrl enter ] Now, I'm hiding over here if I come over and hide, right-click hide.
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 [ F2 ] great trick, I learned years ago from the MrExcel Message Board.
Subtotal with offset to get our sumif, which is really sum 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.
MrExcel: Mike! that is one awesome formula.
I wonder, I couldn't come up with it there, live at the seminar.
I was headed in completely the wrong direction.
But when on 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 in helper 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.
Alright! 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 gonna 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 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 sumifs 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 sum 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 a helper 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 when the row is hidden and then point the sumif that column.
Well, there you have it.
Thanks for stopping by, we'll see next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
