reason formulas might not drag successfully?

[Jennifre]

I'm dragging some formulas down some cells, to apply them there updated along a series of cells, which has worked in the past, using this same formula on the same worksheet. Yet it's not working now, alas.

Instead, I am needing to update the correct cell numbers into the formula for every cell I want it to "work" in.

Is there a fix to this, or something I must be doing wrong?

Thanks!

 

[dave3009]

Do you see $ around the cell references, like $A$1?

Can you post the function you are currently using.

Thanks

 

[Jennifre]

Hi, thanks for your response!

I can post it, though I've moved the $s around on occasion and that has not affected the formula, that I've been able to tell, so far.

It's just today using it, oddly, that it seems different.

In fact, after just adding one more $ to my formula (new ones, red):
=COUNTIF($ce$2:$ce$1364,ROWS($ce$1366:ce1366))

Dragging it down cells in a column worked fine, so I was excited. But then dragged it across cells in a row, and it did what I'm encountering now -- it just copies the same figures so I get several columns of the same figures.

*Can you fill me in or refer me to some good reference to explain how, exactly, various parts of formulas work? This generally isn't coming... naturally to me, though I've been able to figure out other "languages" and code types pretty easily. Excel is... confusing me!

Thanks so much.

 

[dave3009]

Not sure where to start here for you

If you want to fix a column reference in a formula that you drag about then you put a $ infront of the column reference, likewise with the row, and to hard fix a cell you do it infront of both

$A1 will fix column A no matter how far across the function is dragged/copied
A$1 will fix Row 1 no matter how far down the function is dragged/copied
$A$1 will fix that cell no matter how far across or down the function is dragged/copied

This bit of your formula ROWS($ce$1366:ce1366) will return 1 in the first cell it is used in, because there is only one row, however when you drag this down the last part will change so the next row will be 2, and so on.

What is you want to function to do? Is it a count of 1 in CE2:CE1366 or as you drag it down you want to count 2, 3, 4 etc as you drag it? Or something else?

 

[Jennifre]

Hi, thanks! Yes, I'm hoping to do exactly what you're noting here (below), or count 1s, 2s, 3s, 4s, etc, or the values in these cells. So as I drag across and down, I would love the formula to update itself versus my needing to update it in each cell.

"Is it a count of 1 in CE2:CE1366 or as you drag it down you want to count 2, 3, 4 etc as you drag it?"

So, by your explanation, it seems like this formula should work if dragged. However, if I use it in one cell, it works, then I drag that down a column, that works. But when I drag it across rows, then it copies the first column's results in each column, not updating the cell references going across. ??

I'm [obviously] not sure why?

 

[dave3009]

Probably just drop the $ from the front of the column reference then.

=COUNTIF(ce$2:ce$1364,ROWS($ce$1366:ce1366))

 

[Jennifre]

that worked, thanks dave! I don't know why it didn't work before. So it seems just move those around to see what works/what doesn't.