Lookup headers and use COUNTA to sum the data unde

问题:

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

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))‌​)

回答1:

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

---

Edit

This version is dynamic:

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

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.