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

Question

Hello Dear Community,&nbsp;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.&nbsp;Area&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; indicator&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;no. of 5s&nbsp; &nbsp; &nbsp; no. of 4s&nbsp; &nbsp; &nbsp;no. of 3s&nbsp; &nbsp; &nbsp;&nbsp;no. of 2s&nbsp; &nbsp; &nbsp; &nbsp;no. of 1sBadin&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Food was good&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp;Can this obtained by one formula? i need that.&nbsp;&nbsp;&nbsp;

hansvogelaar · Answer

Najam_Ahmed&nbsp;
See the attached version. It contains both a pivot table and a formula solution.

sergeibaklan · Answer

Najam_Ahmed&nbsp;
Few more variants with help of Power Query

peterbartholomew1 · Answer

Najam_Ahmed&nbsp;I do so detest the nested array limitation of Excel.&nbsp; 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.&nbsp; 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)
  )&nbsp;

najam_ahmed · Answer

Thank you so much

najam_ahmed · Answer

This helps alot thank you

Forum Discussion

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

Share

Resources