excel - Excel - 根据所选月份求和 QTD 值

Question

Desired end state: The QTD field sums the quarterly values to date so as an example for October it would return just the October value (1st month in Q4) but when November is selected as the driver the QTD field would sum Oct + Nov values (忽略 Dec 的值）。

我有一个包含驱动程序的电子表格，当前日期将每天更新。此日期驱动“当前月份”字段，我希望从中重新计算 QTD 值。

请参阅下面链接的示例。预期结果 - 11 月第 1 类 QTD 为 3,387,280；4 月的 Cat13 QTD 将为 1,121,021，依此类推

提前致谢！

标签： excelexcel-formula

Answer 1

替代方法使用SUMPRODUCT：

=SUMPRODUCT(C6:N6,--(("Q"&ROUNDUP(MONTH(1&$A$2)/3,0))=$C$1:$N$1),--($C$4:$N$4<=MONTH(1&$A$2)))

结果：