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

I was able to get this working with --(Offset(A2:A9="jeff",0,F1)=1) where F1
is a value representing the column I want to use in my filtering.

Thank you. Still curious about the filtered range issue if that's possible.

"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!