CODEWITHSUNDEEP
excelpivot-table
wildcolor
wildcolor

Reputation: 574

How to count the values in ranges in Excel

I have two columns as shown below. Group values is 0,1,2,3,4 and scores is from 0 to 80. I want to count how many 0s (1s, 2s, 3s, 4s) are present for scores between 0 and 10; 10 and 20; 20 and 30 etc.

I am thinking to use Excel pivot table. But I am stuck - how could I achieve this?

Group scores
1   8.56163
2   34.3649
2   12.2291
0   8.75357
2   8.75967
2   5.87806
0   9.33751
2   32.0303
0   43.5567
2   11.1044
2   24.9266
1   18.9314

-------- result should look like below --------

scores  group   count

0-10    0        2
0-10    1        1
0-10    2        2
0-10    3        0
0-10    4        0

10-20   0        0
10-20   1        1
10-20   2        2
...

------ PS I have solved this problem using . But it would be nice to see someone do it in Excel.

---------------- thanks for all the anwers. I really appreciate it. I have accepted the 1st answer.

Upvotes: 2

Views: 10541

Answers (4)

pnuts
pnuts

Reputation: 59460

Please try:

SO38876372 first example

This uses Grouping (by decade) for the Row labels.

To Group, right-click on one of the entries under Row Labels and Group..., then select enter Starting at:, Ending at: and By: to suit:

SO38876372 second example

Upvotes: 1

Devin Roberts
Devin Roberts

Reputation: 87

You could just use simple countif formulas:

Type out first criteria into cells. D1 = 0, E1 = 1, F1 = 2 etc.

Now you can just say =COUNTIF($A$2:$A$13,D1) and just drag that out.

The other column would require countifs.

Lets say D3 is blank E3 = ">10", F3 = ">20", etc.

Now D4 = "<=10", E4 = "<=20", F4 = "<=30", etc.

Now you can use =COUNTIF($B$2:$B$13,D4) for your first criteria and =COUNTIFS($B$2:$B$13,E4,$B$2:$B$13,E3) for the next criteria and just drag that out.

Hope this helps, good luck!

Upvotes: 1

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

One option is to use a pivot table, but another option is to use COUNTIFS, e.g.:

=COUNTIFS($A$2:$A$13,"="&$F2,$B$2:$B$13,">="&D2,$B$2:$B$13,"<="&E2)

In practice:

enter image description here

Upvotes: 1

Ulli Schmid
Ulli Schmid

Reputation: 1167

I apologize for my previous answer. You can do binning with PivotTable.

  • select your whole two columns (A1:B13), insert PivotTable
  • under rows, put your "Group"
  • under columns, put your "scores"
  • under values, put your "Group"
  • click that last one ("Group" within the values quadrant) and change it to count, not sum

intermediate result:

enter image description here

Now in the resulting pivot table, right click on a colum and select "Group and show detail". You can configure your bins there.

Result: enter image description here

Upvotes: 1

Related Questions