Subtotal, Sumproduct, Multiple Criteria & Offset u

Question

My current WORKING formula:

=SUMPRODUCT((WBS!$P$15:$P$65002=WBS!$A$13)*(WBS!$Q$15:$Q$65002=WBS!$AJ3),WBS!$AK$15:$AK$65002)

I want to convert this formula to total filtered cells only. This is what I have (NOT WORKING) so far:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($AK15,ROW($AK15:$AK65000)-ROW(AK15),,1 WBS!$P$15:$P$65002=WBS!$A$13)*(WBS!$Q$15:$Q$65002=WBS!$AJ3))

Then, if you know how to convert this to grab from dynamic ranges, that would be awesome!

Answer 1

Give this a try:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(WBS!$AK$15,ROW(WBS!$AK$15:$AK$65002)-ROW(WBS!$AK$15),,,1)),--(WBS!$P$15:$P$65002=WBS!$A$13),--(WBS!$Q$15:$Q$65002=WBS!$AJ3))