Count number of cells that contain text but exclude cells that contain quotation marks

[Cantyman]

Hi,

I have a workbook that we use to log vessel information and loading information. In the first column A we enter the vessel name,then along the row we enter the other required info. If a vessel has multiple loadings, we enter " (quotation marks) to indicate the information is the same as the cell above. We want to be able to have a count of the total number of vessels loaded and I figured the easiest way would be to count the number of cells with text in them, to this end I used the formula;

=COUNTA(A7:A36)

The problem with this is it also counts the cells with quotation marks, which we do not want to do as it a second, third etc loading of the vessel, we only want to count the original name of the vessel. to this end I tried;

=COUNTIF(A7:A36,"<>~""")

for some reason this gave me a count of 30 no matter what I entered, deleted, cleared content of cell etc. Is there a formula I can use to only count text excluding quotation marks, or all punctuation marks if that is simpler. Or, what about just counting cells that contain Alphabetic characters (A to Z both capitals and small letters). I have attached a example of our workbook for clarification which has the formula =COUNTIF(A7:A36,"<>~""")
in use but not working in cell A37Prince Rupert Visual ID and Berlese Vessel Infestation Checks test.xls

I truly appreciate any and all help anyone can give me before I pull all my hair out..what little I have left....LOL

Many Many Thanks

[zbor]

Maybe: =COUNTA(A7:A36)-COUNTIF(A7:A36,"""")

[NBVC]

Try:

=SUMPRODUCT(--(A7:A36<>""),--(A7:A36<>""""))

[Cantyman]

Maybe: =COUNTA(A7:A36)-COUNTIF(A7:A36,"""")

Awesome that worked perfectly! I really appreciate the help!

[Cantyman]

Thank you too NBVC for your help, its great to find such knowledge in one place.