Formula help

[Coachclw87]

I have a set a columns that are listed below:

P9: 34R+ 30H+
P10: 34R-
P11: 21D+ 1H#
P12: 34R- 21H+
P13: 1H+

I am using the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(" "&'Code Chart'!$D$5," "&$P$9:$P$13)))) in another cell to count the number of cells that contain 1H. This formula should result with answer being 2. With P11 and P13 both containing 1H In the formula above it is pulling 1H from a cell in a different sheet. This formula works for me just fine. The result of the formula will be in a certain cell. In a different cell I need a formula that counts the number of cells that contain "D" as well as 1H. I know the answer I am looking for based on the columns above should be 1 with P11 being the one cell that contains both.

Any suggestions?

 

[MARZIOTULLIO]

=countifs(p913,"*d*",p913,"*1h*")

 

[AhoyNC]

See if this works for you.
@MARZIOTULLIO - There is an issue with your formula in that if cell P12 had a D in it it would add this to the count, but in a previous post OP stated that 21H and 1H are different, so things like 31H, 21H etc. would not be included in a count for 1H.

Excel Workbook
	P	Q
7		Contains "D" and 1H
8		1
9	34R+ 30H+
10	34R-
11	21D+ 1H#
12	34R- 21H+
13	1H
Sheet

 

[Coachclw87]

I have to use the formula I put in my post for a few reasons. If I use the formula you provided, it will count 21H as 1H cause it has 1H in it. So the formula I am using takes into account a space in front of 1H. Also, the 1H may not always be what I am looking for...so in the formula I have in my first post is pulling from a cell from a different sheet. What ever is in that cell that particular day I am using it is what I need it to count. The "*d*" part I know but I can't get it to work.