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
Welcome to the forum, Matt.
It's perfectly possible, but you have to use a combination of functions to achieve this result. If you attach an example and explain the expected result, we can have a go?
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
Hi mzag and welcome to the forum,
Maybe a pivot table is more useful and easier to create?
see attached.
One test is worth a thousand opinions.
Click the * below to say thanks.
Hi Matt,
Put this formula in D24 and copy across to E24
=SUMPRODUCT(SUBTOTAL(2,OFFSET(D2,ROW(D2:D22)-ROW(D2),,1)),$B$2:$B$22,D2:D22)
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.
Re: SUMPRODUCT formula help
Colin it worked!!!
Thanks so much for this. Truly amazing. Now I just need to memorize that!
Thanks,
Matt
Looking at it again, it can be simplified:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(D2,ROW(D2:D22)-ROW(D2),,1)),$B$2:$B$22)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks