Forum Discussion
Excel formula
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")
HansVogelaar I’ve sent a basic form of what I’m trying to do, so the type of garden is shown with a 1, herbicide use with a y or n.
See the attached version.
The second sheet has formulas and a chart.
I also added an alternative layout to the first sheet, and a pivot table based on this layout.
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.
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!