Excel Copy COUNTIF while retaining reference

[cl6124]

I have the following COUNTIF that is working just how I want it to. Problems come when trying to copy the formula on down. The formula is:

=COUNTIF('Sheet1'!$B1:$B1048576,"=A1")

When I try to copy the formula down the column, I get this:

=COUNTIF('Sheet1'!#REF!,"=A1")

Two issues here that I put in bold. First, I'd like to retain the cells referenced on Sheet1 when copying and second, I'd like the cell A1 to change to A2, A3, and so on.

Any help is appreciated.

 

[Becky]

Welcome to the forum

If you want your range to be the whole of column B then you can type B:B (or $B:$B if you want it to be fixed). For your criteria, you currently have "=A1" but if you change it to simply A1 then when you drag the formula down the column it will change to A2, A3 etc.

So your formula will look like this:

=COUNTIF(Sheet1!B:B,A1)

 

[cl6124]

Awesome! That's exactly what I needed.

Not sure if you could, but I have one last one I need assistance with that I can't seem to get right. If I need to open another thread, please let me know.

I have this one working for me perfectly for me located in F4.

=IF(E4=E3,SUM(F3+1),1)

I'm copying this straight down and its working perfectly, until E54, which is blank, comes in. After E55, the sheet counts up by 1 for each cell to the end of the sheet. I'd like to have it show blank if the bolded section cell value is null.

Again, thanks for your help!

 

[Becky]

Glad it worked for you!

Regarding your other formula, could you describe what you want the formula to do?

Looking at your formula, if the cell to the left is equal to the cell above it, your result is the cell above the formula cell plus one. If the cell to the left is not equal to the cell above it, the result is one. So this appears to count the number of times the number to the left has appeared in sequence - is that right?

 

[cl6124]

Correct. It counts how many consecutive times the values in column E appears. When it differs, the count returns to 1. The problem occurs when the data ends in column E and it treats all cells with a null value as equal and the perpetual count continues.

 

[Becky]

Hmmm that's a tricky one... Got a very busy few days coming up, but leave it with me and I'll have a think

 

[cl6124]

I think I figured it out myself after about an hour of work! Check it out.

=IF(E4=E3,IF(AND(E3="",E4="")," ",SUM(F3+1)),IF(E4=""," ",1))

 

[Becky]

Good work! Glad you managed to figure a solution