COUNTIF range exclude header

0
votes
=COUNTIF('sheet'!A:A, "*Pdf*")

This formula gives me no problem in general, but because the word pdf is part of the header cell, it gives me a wrong value from what I am actually trying to count (the actual data)

Some people have suggested that I select the range by doing shift+ctrl+down arrow, and it seems to work seamlessly for sum formula, (as in if I add in more values under the said range, it automatically adjusts the value) but when it is with countif, using that solution will not include any new data input.

The whole reason I want to have the range be the whole row is so that when new data gets added, the formula will automatically adjust itself.

So ultimately what I am trying to do is have the whole row selected as a range, except for the header cell (which in this case would be A1) Changing A:A to A2:A didn't work either.

3
excelrangerowcountif
What about just =Countif(A2:A1048576,"pdf")? - BruceWayne
try =COUNTIF('sheet'!A2:INDEX('sheet'!A:A, MATCH("zzz", 'sheet'!A:A)), "*pdf*") The approximate match to zzz will return the last row in column A with any text in it. - user4039065

3 Answers

-1
votes

This should do the trick

  =COUNTIF('sheet'!$A2$:A, "*Pdf*")
-1
votes
=COUNTIF('sheet'!A:A, "*Pdf*")-1
-2
votes

This formula should work:

=COUNTIF($A$2:A, "pdf")