Count if a cell contains a specific number AND any kind of text

[macky18]

I have 15 different columns on my spreadsheet. In a drop down box you can select from 1,2,3 or 4.
From M26:M39 is the "other" column. I still want to put in a 1-4 followed by a brief explanation. For example "1 Eczema" or 3 "Vision". The box must be completed with a number followed by a word or short description of a condition. The condition could be anything though so not specific text.

In cells Q7, R7, S7, T7 I want to count how many 1s, 2s, 3s or 4s there are along with the text. So it would count "1 Eczema" in M26 and "3 Vision" in M27 with "2" as the result.

I can see my explanation of this is a little vague, I'm struggling to find the words to describe my problem, so I have attached a file that will hopefully help clarify my issue.

Thanks,

Neil.

[dilipandey]

Hi Neil,

See the attached file, yellow cells, where I have used below formula:-

=COUNT(IF(ISNUMBER(SEARCH(LEFT(Q7,1)*1,$M$6:$M$39)),ROW($M$6:$M$39),""))

enter formula using ctrl shift enter key combination..

Book2.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[benishiryo]

hi macky18, an alternative in Q15 to copy across:
=SUMPRODUCT(--(LEFT($M$6:$M$39)=COLUMNS($Q15:Q15)&""))

[macky18]

Thank you very much, both excellent solutions to my problem.

[dilipandey]

You are welcome...

Cheers


Regards,
DILIPandey
<click on below * if this helps>