SUMPRODUCT formula help

Hi everyone,

I am trying to use the SUMPRODUCT formula to calculate some fields in my worksheet, but whenever I filter the information, the formula does not adjust to reflect the visible rows/columns. When I use the SUBTOTAL formula, the formula does adjust when filtering and only calculates visible rows. I have tried to manipulate the SUBTOTAL formula to result in the same mathematical answers, but I am having no luck. Is there a way to have the SUMPRODUCT formula calculate only visible entries? Or another formula I can use/manipulate to yield the same result?

I would really appreciate any help!!

Thanks

Matt

Hi Colin,

Thanks for the help!

I have attached the document to this message. So what I am trying to do is create a formula that multiplies entries in column B by column D and then Subtotal the results. Cell D24 does this with the SUMPRODUCT formula, but when I try to filter results in column C (show only the 'a' or 'b' entries), the result in D24 does not adjust to reflect only the visible entries. Please let me know how I can change that!

Thanks,

Matt

Thanks for the response!

The pivot table does not seem to multiply the hours by the deal and then sum each. It is just subtotaling the deal.

Colin it worked!!!

Thanks so much for this. Truly amazing. Now I just need to memorize that!

Thanks,

Matt