Excel formula query

[CLARE83]

Hi, I'm wanting a formula that will count a cell if it contains 1 and or 1a, 1b, 1c etc. I currently use "1?" but this only counts the cells with 1a,1b,1c in any ideas how I can get it to count the 1 cells aswell?

Hope it makes sense.

Thanks in advance

Clare

 

[admiral100]

Hi

Try this Ctrl+Shift+Enter NOT just Enter

=COUNT(FIND("1",D14:D18&""))

 

[CLARE83]

Thanks for that although I couldn't get it to work, I have copied an example below in which I want to count how many "1" or "1a" "1b" or "1c" appear in row S - i currently use COUNTIF(marksheet name!S$2:$S251,"1?") but that only counts the cells with a a,b,or c at the end not the whole number cells, hope i'm making sense.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

name 1

F

9KN

9A5/En 17/18

BOSCOBEL

K

Y

92

L

3

8.85

2.8

1A

1

1

name 2

M

9AP

9A3/En 17/18

ATTINGHAM

N

Y

96

L

4

14.2

4.7

3A

3

2C

name 3

M

9FR

9A3/En 17/18

ATTINGHAM

N

Y

96

M

5

16.9

5.6

4A

4

2C

name 4

F

9FR

9B4/En 17/18

ATTINGHAM

N

Y

Y

100

L

4

14.6

4.9

3A

3

1B

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col span="4"><col><col><col><col><col><col><col><col><col></colgroup>

 

[admiral100]

Ctrl+Shift+Enter
=COUNT(FIND("1",'marksheet name'!S2:S5))

It's work for me...