Search code examples
excel-formulasubtotal

Lookup headers and use COUNTA to sum the data under it

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

data

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

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

    enter image description here

    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.