"TEXT" numbers - count problem

L

Len Dolby

Team position 1 is entered as '1 (i.e 1 preceded by a single quote) and
there's more than one numeric team position

When I try to "count" the number of occurrences of " '1 "in a data row
which includes both
text '1 and numeric 1 (score), Excel treats all cells as numeric.

How to count " '1 " and Numeric 1 in a data row, please ? Example -

A1 A2 A3 A4
'1 '1 1 COUNTIF(A1..A4,1 yields 3, (correct answer 1) and
I've yet to establish correct syntax
for counting "'1 " ( correct answer 2).

HELP please !
 
T

Tom Ogilvy

=SUMPRODUCT(ISTEXT(A1:A5)*(A1:A5="1"))

=SUMPRODUCT(ISNumber(A1:A5)*(A1:A5=1))

worked for me.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

basketball scoring 0
Problems with count, counta and countifs 6
Removing Sequential Numbers 5
Create if partial match then copy and paste data from one cell to another 1
Sorting Alphanumeric data 6
Need to count cells in Column1 where each cell's row also hasspecified value in Column2 2
Count number of text rows??? 3
Calculating the number of unique values within a large range. 5

Top