小计，副产品，多重标准&使用动态范围的偏移 [英] Subtotal, Sumproduct, Multiple Criteria & Offset using a Dynamic Range

问题描述

我目前的工作公式：

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

我只想将此公式转换为总过滤单元格。这是我到目前为止（NOT WORKING）：

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!

推荐答案

尝试一下：

=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))

这篇关于小计，副产品，多重标准&使用动态范围的偏移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！