Help with countif?

[Bee008]

Countif example.xlsx

Hello
Just wondering if someone can help me with the countif function?

I have uploaded an example of my spreadsheet. I'm trying to copy the countif formula, but am having the following problems:

As you can see, from C 50 (all the way down to C299) I need to input the countif function (it's covering/counting data from C2 - C46).
However, when I drag the formula down, from C50, the range is changed. Is there a way to drag the countif formula down from C50-C299 without changing the range (the range still needs to be C2-C46)?

Furthermore, as the formula is dragged down, I need the countif criteria to change. For example, C50 needs to be 1, C51 needs to be 2, and so on until number 250! Is there anyway this can be done other than manually changing each formula?

I'm going to need to apply this to 30 participants (30 columns), so that's 7,500 times I'm going to be needing to use the countif formula!

Thank you so much for any input!!

[jwillis07]

Hi Bee008,

You just need to change the range to an absolute reference - that will stop it changing then.

In cell C50, enter...

=COUNTIF($C$2:$C$46,$A50)

[Bee008]

Ah brilliant, thank you - I didn't know such a thing existed (Excel newbie over here!!)

Is there a way to get the criteria to change from 1 all the way down to 250 though? Other than manually entering it

[jwillis07]

Yes that formula will do that. Instead of having...

=COUNTIF($C$2:$C$46,1)

In this formula 1 is the criteria to look up, but in your work book, you have the numbers 1 - 250 in cells A50 onwards. So by tweaking the formula to...

=COUNTIF($C$2:$C$46,$A50)

A50 is now the criteria to look up - which in this case, contains 1 - when dragging down, this will change to be 2, 3, 4 and so on.

Hope this helps.

[Sam Capricci]

when you enter a number in excel, if you enter the next in a sequence like 1 then 2, excel will auto fill if you just drag down (or across) as it sees a series.

[Bee008]

Yes that formula will do that. Instead of having...

=COUNTIF($C$2:$C$46,1)

In this formula 1 is the criteria to look up, but in your work book, you have the numbers 1 - 250 in cells A50 onwards. So by tweaking the formula to...

=COUNTIF($C$2:$C$46,$A50)

A50 is now the criteria to look up - which in this case, contains 1 - when dragging down, this will change to be 2, 3, 4 and so on.

Hope this helps.

Perfect!

Thank you very much jwillis07