Microsoft Excel COUNTIF Formula

I need to copy the below formula to about 1000 cells
=COUNTIF(B2:B1500,A2000)
=COUNTIF(B2:B1500,A2001)
=COUNTIF(B2:B1500,A2002)
=COUNTIF(B2:B1500,A2003)
=COUNTIF(B2:B1500,A2004)
=COUNTIF(B2:B1500,A2005)
The first portion (ie B2:B1500 ) must remain constant and the second portion (ie A2000) must vary from 2000 to 3000. I tried to drag the formula to 1000 cells , but the first portion is also changing.
Is there any way to solve this problem.
Answer #1
go to an empty box
type +b2:b1500 (or w\e it is.. you should know)
enter
right click that box
define name and call it ‘bedtime’
then go to the formula and use
=COUNTIF(bedtime,A2000)
=COUNTIF(bedtime,A2001)
=COUNTIF(bedtime,A2002)
=COUNTIF(bedtime,A2003)
=COUNTIF(bedtime,A2004)
=COUNTIF(bedtime,A2005) and you have it fixed..
if i managed to stay awake the whole post..
gn…
Answer #2
Thanks
Answer #3
i hope you won’t have to name cells every time you want to fix the value
It’s easier to use the dollar sign, so in this case:
=COUNTIF($B$2:$B$1500,A2000)
Answer #4
Thanks