Criteria parameter from CountIf function doesn't work when using reference cell

[vladi305]

Is there's a way to use this number on the criteria parameter dynamically instead of fixed number. This 17 represents a range of numbers like the ones on top from the pic. When I use the cell in reference on top I get 0 instead of 1. The format from cells on top is General but I tried numbers and itsn't working either. The numbers are the one in blue

=IF(COUNTIF($B22:$F22,17)=0,"", COUNTIF($B22:$F22,17))

 

[vladi305]

I think I fixed: =IF(COUNTIF($B25:$F25,"=" & CO1 & "")=0,"", COUNTIF($B25:$F25,"=" & CO1 & ""))
No it doesn't update lol
I get =IF(COUNTIF($B22:$F22,"=" &#REF! & "")=0,"", COUNTIF($B22:$F22,"=" &#REF! & ""))

 

[Rick Rothstein]

What do you mean when you say "17 represents a range of numbers"? What exactly are you trying to count?

 

[vladi305]

What do you mean when you say "17 represents a range of numbers"? What exactly are you trying to count?

17 is a reference number like those on the 60s on top. Yes is a range on number being used as reference to lookup on another range of number from columns B to F
and it counts how often an event is happening

 

[Rick Rothstein]

So, you don't want to put 17 into your formula directly, correct? In that case, your only other option is to put the value in a cell and use the cell reference in your formula. Let's say, for example purposes, that the cell you want to use for this is A22, then your formula would become...

=IF(COUNTIF($B22:$F22,A22)=0,"", COUNTIF($B22:$F22,A22))

Type any reference number in A22 and the count of that reference number in B22:F22 will be returned by the formula.

 

[vladi305]

So, you don't want to put 17 into your formula directly, correct? In that case, your only other option is to put the value in a cell and use the cell reference in your formula. Let's say, for example purposes, that the cell you want to use for this is A22, then your formula would become...

=IF(COUNTIF($B22:$F22,A22)=0,"", COUNTIF($B22:$F22,A22))

Type any reference number in A22 and the count of that reference number in B22:F22 will be returned by the formula.

Exactly, if I use a fixed number I'd need to change it twice on the formula to update the rest of the table but if I use the cell in reference then it will be just copy and paste but thing is that it doesn't return the 1 and it's why I posted the issue

 

[Rick Rothstein]

Exactly, if I use a fixed number I'd need to change it twice on the formula to update the rest of the table but if I use the cell in reference then it will be just copy and paste but thing is that it doesn't return the 1 and it's why I posted the issue

I am not sure what to tell you... using the cell reference in place of the 17 in the formula you posted worked fine for me.

 

[vladi305]

I am not sure what to tell you... using the cell reference in place of the 17 in the formula you posted worked fine for me.

Maybe is some format on the cells, I'll check and follow up with the results

 

[vladi305]

I am not sure what to tell you... using the cell reference in place of the 17 in the formula you posted worked fine for me.

Ok i found the issue I was having. I wasn't using the absolute cell reference.

I had to give credit to

pjmorris​

He mentioned me this earlier in another thread

Because I wasn't using the $ then when dragging down the formula it wouldn't return the expected value

Now is good: =IF(COUNTIF($B22:$F22,DG$1)=0,"", COUNTIF($B22:$F22,DG$1))

Thanks anyway for help Rick

 

[pjmorris]

thanks for the acknowledgement and delighted you've fixed it.