0

I'm trying to calculate the count of multiple occurrences of a figure using countif.

I have the range set but I need a calculation which can count in multiples of 50 without me having to type thousands of versions of countif (=COUNTIF(B2:B5,">=50")-COUNTIF(B2:B5,">100" etc.).

Data    Count

50         1
70         1
80         1 
10         0
150        3

This data should show 6 but at the moment I'm getting 4.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
user5309171
  • 1

2 Answers2

1

First you can start by making bins. you can make it with Data analysis tool or half manual

Like in the example, on A2 enter 0 and on b2 enter =a2+50

Same goes for a3 enter =b2 and last on a4 =a3+50

Now you can drag it down as much as you like.

Instaed of using countif use sumif finction. let's assume your data is on cloumn H and the values you want to sum are in column I, then on c2 enter =SUMIFS(I:I,H:H,">"&A2,H:H,"<="&B2)

you can drag it down as much as you like.

enter image description here

Balinti
  • 1,524
  • 1
  • 11
  • 14
0

Simply use Excels ROUNDDOWN function:

e.g. for B2: =ROUNDDOWN(A2/50,0)

Klaster
  • 673
  • 1
  • 7
  • 17
  • 1
    That provides the same result as the screenshot in the OP, but it does add up to 6, which is something I was questioning. :) – rwking Sep 07 '15 at 13:29
  • I admit that my answer is based on many assumptions about what the OP wants and is therefore just as good as the question itself. – Klaster Sep 07 '15 at 15:12