Forum Discussion

StevieBrett's avatar
StevieBrett
Copper Contributor
Mar 07, 2021
Solved

Excel formula

Hi I have a column with 66 entries of different areas of m2. How can I sort these into 5 groups, ie. under 100, 100-500, 500-1000,1000-10,000, 10,000+. Thanks
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Mar 07, 2021

    StevieBrett 

    You could create a pivot table based on the data.

    Add the new column to both the Rows area and the Values area.

     

    Or use formulas such as

     

    =COUNTIFS(A2:A67, "<100")

    =COUNTIFS(A2:A67, ">=100", A2:A67, "<500")

    =COUNTIFS(A2:A67, ">=500", A2:A67, "<1000")

    =COUNTIFS(A2:A67, ">=1000", A2:A67, "<10000")

    =COUNTIFS(A2:A67, ">=10000")

StevieBrett's avatar
StevieBrett
Copper Contributor
Mar 08, 2021

HansVogelaar that’s brilliant, thanks. So it should be possible to compare any of the variables, so private that have cobbles and use herbicide? Sorry to have taken up so much of your time, I really appreciate your help!

HansVogelaar's avatar
HansVogelaar
MVP
Mar 09, 2021

StevieBrett 

 

See the attached version. You can select a variable in A2 and in B2 on Sheet2, and C2 will display the count. The result will only be meaningful for variables (columns) that contain numbers, not for columns that contain text.

If you want to be able to count the latter too, you'd need to change the layout to one that is suitable for creating a pivot table - see the example in my previous reply. Pivot tables are very flexible.

Resources