SUMIF Visible Cells

October 27, 2017 - by Bill Jelen

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