Count Formula based on column and row

[Hunter Childress]

Please see the attached. I am trying to automate a formula in cell D12 that counts based on the row output and the name. I am thinking Offset may need to be used to capture both criteria.

I am trying to create a formula I can drag down and across that will automatically update.

Thank you!

 

[Peter Bartholomew]

This uses the 365 MAKEARRAY helper function to build the result table as a single dynamic array.

Worksheet formula:
= MAKEARRAY(ROWS(name), 4, Occurrencesλ)

Occurrencesλ
= LAMBDA(
    LET(
      rangetoSearch,   INDEX(dataTable, 3 * n, 0),
      categorytoCount, INDEX(trainingCategory, c),
      COUNTIF(rangetoSearch, categorytoCount)
    )
  )

 

[bosco_yip]

Try,

In D12, formula copied across and down:

=SUM(ISNUMBER(OFFSET($C$2,MATCH($C12,$C$3:$C$8,0),6,,25))*(OFFSET($C$2,MATCH($C12,$C$3:$C$8,0)+2,6,,25)=D$11))

Or,

=SUM(OFFSET($C$2,MATCH($C12,$C$3:$C$8,0),6,,25)*(OFFSET($C$2,MATCH($C12,$C$3:$C$8,0)+2,6,,25)=D$11))

Remark: please confirm which above formula result is your desired result

 

[Hunter Childress]

This uses the 365 MAKEARRAY helper function to build the result table as a single dynamic array.

Worksheet formula:
= MAKEARRAY(ROWS(name), 4, Occurrencesλ)

Occurrencesλ
= LAMBDA(
    LET(
      rangetoSearch,   INDEX(dataTable, 3 * n, 0),
      categorytoCount, INDEX(trainingCategory, c),
      COUNTIF(rangetoSearch, categorytoCount)
    )
  )

View attachment 85458

Thank you Peter. Fascinating process. I was able to replicate on my end and it works like a charm.