Copying COUNTIF function down column increases each row by 1 (as a series)

**Moonbather** · 01-14-2013, 06:08 PM

I have values in cells A1-A11.

In cells C1-C11, some of the values match what's in A1-A11 and some of the values do not match.

In cell E1, I entered =COUNTIF(A1:A11,C1), then I copied the formula down through cell E11.

The problem is that as I copy the formula down column E, it increases each row by 1 (similar to the way a series of numbers would fill in). So for example, in cell E2, the formula reads as "=COUNTIF(A2:A12,C2)" instead of "=COUNTIF(A1:A11,C2)".

I don't want it to fill down as a series. How do I fix this?

**ConneXionLost** · 01-14-2013, 06:12 PM

Use:

Please Login or Register  to view this content.

**oeldere** · 01-14-2013, 06:12 PM

Try this one, see the red text.

Please Login or Register  to view this content.

**BB1972** · 01-14-2013, 06:14 PM

Hey Moonbather,

Some absolute referencing, instead of relative (what you have now) should sort this for you. Use $, like so: $A$1:$A$11 in your formula, to reference only those cells while dragging your formula.

Hope this helps.