Filtering/summing

[Ben]

I have an array formula similar to... {=SUM( IF( (RangeName1="This") *
(rangeName2="That"), SumRange) )}
when filtering is applied to the source data, it does not affect the total
of the formula. It still sums the filtered hidden data. How can I sum just
the visible data?

Thanks
Ben

 

[Frank Kabel]

Hi Ben
have a look at the SUBTOTAL function for this

 

[BrianB]

Use =SUBTOTAL(9,SumRange

 

[Ben]

I did try - {=SUBTOTAL(9, IF( (RangeName1="This") * (rangeName2="That"),
SumRange) )}
but, it doesn't seem to like it, I get #VALUE (notice it is an array
formula).

 

[Frank Kabel]

Hi Ben
AFAIK you can't combine SUBTOTAL with this kind of array formulas.
SUBTOTAL just accepts a range as second argument.

 

[Dave Peterson]

A a post from Peo Sjoblom (originally from Laurent Longre):

http://google.com/groups?threadm=1a5001c184be$e5d70050$b1e62ecf@tkmsftngxa04
(one line in your browser)

Showed how to do it to count items.

A minor modification of that formula leads to this:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT("1:"&ROWS(A2:A9))),0))
*(B2:B9="this")*(C2:C9="that"))

(all in one cell)

I had a filter applied to A19 and wanted to subtotal the visible values in
A2:A9 where B2:b9 and C2:c9 were "this" and "that".

 

[Ben]

Thanks for that!!

Is there any way of using named ranges instead?
Such as -
=SUMPRODUCT(SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT("1:"&ROWS(SumRange))),0))
*(ConditionRange1="this")*(ConditionRange1="that"))
.... the above works for SumRange, but I cannot get it working when using the
condition ranges. I get a #N/A!

TIA
Ben

 

[Dave Peterson]

I'm not sure what sumrange and conditionrange1 is, but I don't think you want to
be multiplying the "this" and "that" together.

And remember that it's adding stuff in column A.

Offsetting from 1 to the number of rows in SumRange. So that example summed
A2:A9 if the conditions were correct.

For example, if you put this in a cell by itself:
="1:"&ROWS(A2:A9)
hit F2 followed by F9 (to evaluate it, you'll see it is just a nice way of
typing:

1:8
(rows 2 to 9 is 8 rows).


=SUMPRODUCT(SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT("1:"&ROWS(sumRange))),0))
*(OR(conditionrange1={"this","that"})))