I want my criteria to change when I drag a formula

[ZMF]

Hi guys,

Sorry if this has been solved elsewhere. I usually find solutions on google pretty easily but I don't know that a solution even exists for this one.

Here's my formula: =COUNTIFS('Job 1'!$C:$C,1,'Job 1'!$F:$F,"n")

It works fine, but I need to paste it across many cells and I want my criteria "1" to increase to 2, 3, 4 and son on, the same way a row number changes if you haven't fixed it.

So the next cell down, I want my formula to be =COUNTIFS('Job 1'!$C:$C,2,'Job 1'!$F:$F,"n")
But Excel fixes this criterion - it will remain as 1 if I paste in another cell.

Is there a solution to this?
Thanks very much.

Z

[FDibbins]

Hi and welcome to the forum

Try this...=COUNTIFS('Job 1'!$C:$C,row(A1),'Job 1'!$F:$F,"n")

I notice that you used COUNTIFS() which came with the 2007 version (.xlsx), but your profile says 2003. Please update your profile as necessary, members tailor questions based on your excel version

[TMS]

For the first row:

Please Login or Register  to view this content.

and drag down


Regards, TMS

[ZMF]

Brilliant. Thanks so much. Sorry about my incorrect version.

Z

[FDibbins]

Happy to help