View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 671
Default Filter and SumProduct

JMB,

Thank you for your input; however, my problem is not yet solved (perhaps
because I didn't ask it very well).

You solution works when you know that your are filtering on columns A and B;
however, I was hoping to use this in conjection with a range that is already
filtered using the built in filtering in Excel. In other words, is there a
similar function I could use to calculate the SumProduct of the rows that are
not hidden when a filter is applied to the range of data? Using your formula
I would have to manually change the A2:A9="jeff" and the B2:B9="abc" in order
to apply a different filter to the data.

Your formula is very helpful though, and I will continue working with it to
see if I can play around with it to achieve my desired goal.

Thank you!


"JMB" wrote:

If, for example, you want to total C2:C9 where A2:A9=jeff and B2:B9=abc, try:

=SUMPRODUCT(--(A2:A9="jeff"),--(B2:B9="abc"),SUBTOTAL(9,OFFSET(C2,ROW(C2:C9)-ROW(C2),0)))

Modify as needed.

"Jay" wrote:

I am trying to use a SumProduct function but want to be able to do so on a
filtered set of rows. SumProduct is not one of the functions available with
SUBTOTAL. SumProduct is also not available for any of the "D" functions
associated with database calculations.

If anyone knows how to accomplish this I'd greatly appreciate your response.

Thanks!