# Counting question

#### Zee_fun

##### New Member
Can't seem to figure out the formula. I need to count column "I" and see how many of them are >=81 and <=110. Then the formula will be copied down to count >=111 and <=149. and so on. Can you help?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

##### MrExcel MVP
Book3
ABCDE
1
2123815
31251116
4149150
5135
6164
785
8164
975
10108
1190
1274
13125
1487
15165
16191
1773
18189
19118
2085
Sheet1

The formula in E2, which is copied down up to but excluding the row corresponding to the last value in D, is:

=SUMPRODUCT(--(\$A\$2:\$A\$20>=D2),--(\$A\$2:\$A\$20<D3))

#### gaftalik

##### Well-known Member

Would you write please the sumif and the sum(if variations

Thank you Champ!

##### MrExcel MVP
gaftalik said:
...
Would you write please the sumif and the sum(if variations...

1]

=SUMPRODUCT(--(\$A\$2:\$A\$20>=D2),--(\$A\$2:\$A\$20 < D3))

2]

=COUNTIF(\$A\$2:\$A\$20,">="&D2)-COUNTIF(\$A\$2:\$A\$20,">"&D3)

3]

=SUM((\$A\$2:\$A\$20>=D2)*(\$A\$2:\$A\$20 < D3))

4]

=SUM(IF(\$A\$2:\$A\$20>=D2,IF(\$A\$2:\$A\$20 < D3,1,0),0))

5]

=COUNT(IF(\$A\$2:\$A\$20>=D2,IF(\$A\$2:\$A\$20 < D3,1)))

[3] to [5] requires confirming with control+shift+enter.

And no SumIf for counting.

#### gaftalik

##### Well-known Member

What about dcount champ ?

#### Yogi Anand

##### MrExcel MVP
gaftalik said:
What about dcount champ ?
Here we go ...
Book2
ABCDE
1Field1
212381FALSETRUE
3125111
414914956
5135
6164
785
8164
975
10108
1190
1274
13125
1487
15165
16191
1773
18189
19118
2085
Sheet2

#### gaftalik

##### Well-known Member

As expected, thank you

##### MrExcel MVP
gaftalik said:
What about dcount champ ?

Champ?

Here a method of mine that makes a DCOUNT formula is copiable for many rows of criteria without repeating the header row...
Book3
ABCDEF
1XXXDCount
212381>=81<1115
3125111>=111<1506
4149150
5135
6164
785
8164
975
10108
1190
1274
13125
1487
15165
16191
1773
18189
19118
2085
Sheet1

D2, copied down as far as needed:

=">="&C2

E2, copied down as far as needed:

="<"&C3

F2, copied down as far as needed:

=DCOUNT(\$A\$1:\$A\$20,1,\$D\$1:E2)-SUM(\$F\$1:F1)

The data table method, revealed by MS, is another that allows for not repeating the header row of the criteria range.

For the latter and more see my contrib in:

http://216.92.17.166/board2/viewtopic.php?t=60895

#### gaftalik

##### Well-known Member
That's a knock out !No comment !
Champ your 3rd formula shall be entered as Ctrl+shift +enter isnt it ?
{=SUM((\$A\$2:\$A\$20>=D2)*(\$A\$2:\$A\$20 < D3))}

#### gaftalik

##### Well-known Member
My God! that's incredible !

Replies
4
Views
638
Replies
3
Views
454
Replies
7
Views
361
Replies
3
Views
221
Replies
8
Views
148

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,097
Messages
5,768,071
Members
425,452
Latest member
htay44

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back