View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Filter and SumProduct

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!