Countif function help

Coachclw87 · Dec 8, 2017

I am trying to use the count if function and I am running into a bit of an issue. Just for example I want to count the number of cells that contain the text "1h", so I type the formula =countif(P9:P13,"*1h*")
The answer I am looking for should be 1, but it's coming back as 2. It's counting one of the cells that contains "21h" in the cell. Any suggestions?

mrhstn · Dec 8, 2017

The thing is 21h contains 1h, when you use the asterisk * wildcard it just means anything with a length of zero or more. If the cells containing 1h begin with that then omit the first asterisk, if there is a space before the 1h the change the criteria to "* 1h*".

AhoyNC · Dec 8, 2017

Maybe something like this.

Excel Workbook

P

Q

9

1h

3

10

21h

11

test1h rrrr

12

test 71h yuyuy

13

45 kl 1h

Sheet

Coachclw87 · Dec 8, 2017

I probably should have specified this as well. The criteria I am trying to use is text that is in a cell from another sheet. "Criteria cells" that is on the other sheet will change from use to use...so I can't just use "*h1*". So the way I have the formula right now is =COUNTIF(P9:P13,"*"&'Code Chart'!D5&"*") And there is where I am having the issue, in that "Code Chart" sheet...the cell D5 is =$B$5&$E$3 (where B5 is H and E3 is 1)...so that formula combines it.

Below are the cells for the P9-P12

P9 - 34R+ 30H+
P10 - 34R-
P11 - 21D+ 1H#
P12 - 34R- 21H+
P13 - 37D- 24H!

AhoyNC · Dec 8, 2017

So in your example D5 would = H1. There is no H1 in your cells P9 - P13. There is a 1H, so would the count be 0 or 1?
Your formula would break down to:
COUNTIF(P9:P13,"*H1*")

Coachclw87 · Dec 8, 2017

Sorry, that's my fault. I meant to type 1H

Coachclw87 · Dec 8, 2017

So in B5 it's 1 and in E3 it's H

AhoyNC · Dec 9, 2017

If there will be a space in front of the 1H as in your example, try:

Excel Workbook

P

Q

R

9

34R+ 30H+

10

34R-

1

11

21D+ 1H#

12

34R- 21H+

13

37D- 24H!

Sheet

Coachclw87 · Dec 9, 2017

Awesome! That seemed to work..I will check it with the rest of my entries and hope it works! Thank you very much. I have a couple of questions, what if I didn't have a space in front of the 1H? Would that change the formula, if so...how?

Another question I have is given the formula you gave me, if I wanted to find the count of cells that contained for example the "1H" cell that's in D5 as well lets says the letter "D" or "R+" what would I do then?

Last thing I wanted to ask was, I'm pretty new to these advanced excel formulas. I tried to figure out on my own what the formula you gave me meant but I couldn't figure it out. Can you explain it to me? I really appreciate it!!

AhoyNC · Dec 9, 2017

This formula is limited in that there must be a space in front of the 1H or the 1H needs to be the first 2 characters in the string.

This is how the formula works.

" "&$P$9:$P$13
returns the following array which just puts a space in front of your data which is needed if the 1H is at the start of the string (we are looking for " 1H").
{" 34R+ 30H+";" 34R-";" 21D+ 1H#";" 34R- 21H+";" 37D- 24H!"}

The SEARCH function will return a number of the starting position if it finds the text or an error if it doesn't.
[TABLE="width: 443"]
<tbody>[TR]
[TD]SEARCH(" "&'Code Chart'!$D$5," "&$P$9:$P$13)
returns
[TABLE="width: 443"]
<tbody>[TR]
[TD]{#VALUE!;#VALUE!;6;#VALUE!;#VALUE!}

The ISNUMBER is used to turn the above array into TRUE or FALSE
 [TABLE="class: MsoNormalTable, width: 332"]
<tbody>[TR]
[TD="width: 443"] ISNUMBER(SEARCH(" "&'Code Chart'!$D$5," "&$P$9:$P$13))
returns

[TABLE="width: 443"]
<tbody>[TR]
[TD]{FALSE;FALSE;TRUE;FALSE;FALSE}

The double negative (--) in front of the ISNUMBER turns the TRUE FALSE into 1's and 0's.
--(ISNUMBER(SEARCH(" "&'Code Chart'!$D$5," "&$P$9:$P$13))
returns
[FONT=&quot] {0;0;1;0;0}

The SUMPRODUCT then adds the above array and returns 1 in this case.

At this point I'm not sure there is an easy way to handle your other questions with a formula. It might have to be a VBA solution that someone else could help with.

[/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Countif function help

Coachclw87

New Member

Excel Facts

mrhstn

Active Member

AhoyNC

Well-known Member

Coachclw87

New Member

AhoyNC

Well-known Member

Coachclw87

New Member

Coachclw87

New Member

AhoyNC

Well-known Member

Coachclw87

New Member

AhoyNC

Well-known Member

Similar threads

Forum statistics

Share this page

Countif function help

New Member

Excel Facts

Active Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock