查找标题，并使用COUNTA来计算其下的数据 [英] Lookup headers and use COUNTA to sum the data under it

问题描述

我正在尝试根据月份（ B1 ）找到一个特定的列，并且使用 x 根据指定区域（ D1 ）。

I am trying to find a specific column based on month (B1) and count the number of cells with x under it based on by the designated region (D1).

这是我想到的，但它是回到$ code> #VALUE！。

This is what I figured it would be but it is coming back as #VALUE!.

=SUMPRODUCT(SUBTOTAL(3,INDEX($1:$1048576,0,MATCH($B$1,$3:$3,‌​0))),--(($A:$A=D$1))‌​)

推荐答案

SUBTOTAL不适用于INDEX，使用OFFSET：

SUBTOTAL does not work with INDEX, use OFFSET:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A3,ROW(1:9),MATCH($B$1,3:3,0)-1))*(A4:A12=D1))

编辑

此版本是动态的：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A3,ROW(INDIRECT("1:" & MATCH("zzz",A:A)-3)),MATCH($B$1,3:3,0)-1))*(A4:INDEX(A:A,MATCH("zzz",A:A))=D1))

它将根据列A中的数据自动调整大小。设置标题行在行3中，如果更改则需要更改 3：3 和 -3 到标题所在的行号。

It will automatically resize based on how much data is in Column A. It is set that the title row is in row 3, if that changes then you need to change the 3:3 and the -3 to the row number where the titles are located.

这篇关于查找标题，并使用COUNTA来计算其下的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！