In a column headed "Fred Count" I have the formula =COUNTIF(L$2:L10,"Fred") in row 10 where column L is headed "Person" and contains various names, so as I drag the formula down the "Fred Count" column it shows the number of times Fred appears in Person column at the current row or above.

How do I write the formula to use the table nomenclature? I've tried various combinations, but haven't hit on theone that works.


  • what do you mean by "table nomenclature"? Something like [[#All][Person]]? There is no way for that. You can use only if you refer to the current row, or the whole column. – Máté Juhász Feb 17 '17 at 13:17
  • yes, that's precisely what I meant - thanks. – user3588542 Feb 17 '17 at 13:43
  • (I had been assuming using "table references" I could at least reconstruct the range with an absolute reference to the first data cell in the Person column and a reference to the cell in the current row.) – user3588542 Feb 17 '17 at 13:48
  • You can't refer to the cell in the first row with data, but what's more important: can't compose those two references to be considered as start and end of a range:( (maybe you could with ADDRESS and INDIRECT functions, but this would make your formula too complicated). Maybe you could use this one: =COUNTIF(OFFSET([@[Person]],-(ROW()-2),0,ROW()-1,1),"Fred"). – Máté Juhász Feb 17 '17 at 13:55

While it's not possible to refer part of a column by using structured references, you can combine current row with OFFSET for the desired result:


(Alternatively you could do it with ADDRESS and INDIRECT functions, but this would make your formula too much complicated).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.