Need to find each areas no of 5 for each indicator, and 4s, 3s and 1s

[Najam_Ahmed]

Hello Dear Community,

Hope everyone is doing fine. I have shared an Excel sheet with data that I want to analyze. I want to know the details in the following format.

Area                indicator                         no. of 5s      no. of 4s     no. of 3s      no. of 2s       no. of 1s

Badin         Food was good                         12                 3                 2                 4                   3

Can this obtained by one formula? i need that.

[#2]

@Najam_Ahmed 

See the attached version. It contains both a pivot table and a formula solution.

[#3]

@Najam_Ahmed 

Few more variants with help of Power Query

[#4]

@Najam_Ahmed 

I do so detest the nested array limitation of Excel.  The desired result almost always is an array of arrays and this makes it so difficult to output the entire results using a single dynamic formula.  I do not want to always be searching for the least-worst workaround (in this case using MAKEARRAY)!

= LET(
    distinctAreas,      UNIQUE(Table1[Area]),
    indicatorHeadings,  DROP(Table1[#Headers], , 1),
    indicatorResults,   DROP(Table1, , 1),
    areaAttribute,      TOCOL(IF(ISTEXT(indicatorHeadings), distinctAreas)),
    indicatorAttr,      TOCOL(IF(ISTEXT(distinctAreas), indicatorHeadings)),
    countAreaIndicator, COUNTA(distinctAreas) * COUNTA(indicatorHeadings),
    resultCounts,       MAKEARRAY(countAreaIndicator, 5,
      LAMBDA(r,c,
        LET(
          indicatorResults, INDEX(indicatorResults, 0, 1 + MOD(r - 1, 11)),
          COUNTIFS(Table1[Area], INDEX(areaAttribute, r), indicatorResults, 6-c)
        )
      )
    ),
    HSTACK(areaAttribute, indicatorAttr, resultCounts)
  )

[#5]

Thank you so much

[#6]

This helps alot thank you

[#7]

difficult for me to understand but this gets the results will try to use that.