Using a Variable in Excel for COUNTIF
First time question and I hope it's easier than I'm making this.
Can I use a variable inside a COUNTIF formula?
Currently my formula is:
=COUNTIF($C$2:$C$415,R6)
I would like to have $415 as my variable. I have tried something along the lines of:
D1=415=COUNTIF($C$2:$C$(D1),R6) ..
but obviously get a error.
The reason I need this is column C will constantly be incrementing as I add more rows.
Instead of going into each of my formulas and updated 415 to 416, 417 etc, I would like to just define a Cell that can be my variable, or total rows.
Currently Column C can have blank cells, so I can't have a macro that finds the next empty cell.. but I do however have Column A with a constant populated cell and stops at the last ticket. However Column A is unrelated to the COUNTIF.
UPDATE 1
I'd also like to mention that I'd be using this variable in many formulas in the spreadsheet. Not only COUNTIF's. Also, the COUNTIF contains text.
UPDATE 2
Actually, I figured it out! I am using this formula instead:
=COUNTIF(INDIRECT("C"&D1&":A"&D2),R6)
I'm putting D1=2 and D2=415 and will just update cell D2 with how many rows I have.
I guess I just needed to ask the question thoroughly to fully understand what I wanted!
Thank you in advance for all help, tips and suggestions.
Answer
Would "=COUNTIF($C:$C,R6)" do the trick? This will apply COUNTIF to the whole of column C. It's an easy solution, but probably not the most efficient.