Counting question

Zee_fun

New Member
Joined
Feb 28, 2004
Messages
5
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? :rolleyes:
 

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
Dear Aladin,

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

Thank you Champ!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

What about dcount champ ?
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

As expected, thank you
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
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))}
 
Master Excel Bundle

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.

Forum statistics

Threads
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?

Disable AdBlock

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
Top