Thanks Bob, for you solution. I still have not got my head around how it
works. In fact I have not been able so far to get it to work. I assume your
list starts in column C (the filtered list) and column D the summation
values. I have tried to replicate it but so far I get a total of zero!
In the Offset function it shows row($c2$19) - row($c$1), does this not
always return 1 ? why would you not use 1. In fact why use offset at all, or
this something to do with the array values for the sumproduct function?
Sorry to be a bit thick on this, but I truly am trying to understand it and
how it works rather than just copy paste formulas.
Cheers
Nigel
"Bob Phillips" wrote:
This is an example of counting with a filtered list
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($C$2:$C$1
9="the value"))
and summing
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),
--($C$2:$C$19="IDFM"),$D$2:$D$19)
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Nigel RS" <Nigel wrote in message
...
Hi All
I have been successfully using sumif and countif to group summary results.
I also use subtotal to return sums and counts for autofiltered lists.
How do I combine autofiltered lists into my sumif and countif statements?
I
could write the VBA code but would prefer a worksheet formula solution.
Thanks