Is there a way of counting the number of cells in a column that contain at
least in part a certain string variable that is contained in another cell
using for instance the countif function

e.g.
A B
1 CABG CABG
2 CABG
3 PCI
4 CABG, PCI
5 PCI

=countif(b1:b5, a1)

I want the answer to be 3, but the above function gives an answer of 2
because cell B4 does not fit exactly. However, I do want to use a1 as the
criteria and not just put "*CABG*" instead

You could try it like this:

=countif(b1:b5, a1&"*")

i.e. you use the wildcard within the formula.

Hope this helps.

Pete

On Dec 5, 8:34 pm, JRD wrote:
Is there a way of counting the number of cells in a column that contain at
least in part a certain string variable that is contained in another cell
using for instance the countif function

e.g.
A B
1 CABG CABG
2 CABG
3 PCI
4 CABG, PCI
5 PCI

=countif(b1:b5, a1)

I want the answer to be 3, but the above function gives an answer of 2
because cell B4 does not fit exactly. However, I do want to use a1 as the
criteria and not just put "*CABG*" instead

=countif(b1:b5, "*" & a1 & "*")

JRD wrote:

Is there a way of counting the number of cells in a column that contain at
least in part a certain string variable that is contained in another cell
using for instance the countif function

e.g.
A B
1 CABG CABG
2 CABG
3 PCI
4 CABG, PCI
5 PCI

=countif(b1:b5, a1)

I want the answer to be 3, but the above function gives an answer of 2
because cell B4 does not fit exactly. However, I do want to use a1 as the
criteria and not just put "*CABG*" instead

--

Dave Peterson

Actually, it would be more thorough like this:

=countif(b1:b5, "*"&a1&"*")

so that you could pick up entries like PCI, CABG

Hope this helps.

Pete


On Dec 5, 8:52 pm, Pete_UK wrote:
You could try it like this:

=countif(b1:b5, a1&"*")

i.e. you use the wildcard within the formula.

Hope this helps.

Pete

On Dec 5, 8:34 pm, JRD wrote:



Is there a way of counting the number of cells in a column that contain at
least in part a certain string variable that is contained in another cell
using for instance the countif function

e.g.
A B
1 CABG CABG
2 CABG
3 PCI
4 CABG, PCI
5 PCI

=countif(b1:b5, a1)

I want the answer to be 3, but the above function gives an answer of 2
because cell B4 does not fit exactly. However, I do want to use a1 as the
criteria and not just put "*CABG*" instead- Hide quoted text -

- Show quoted text -

Does this formula still hold true if one is using sumproduct e.g.

e.g.
A B C
1 CABG CABG Yes
2 CABG No
3 PCI No
4 CABG, PCI Yes
5 PCI Yes

So if I want to count the number of rows in which both Column B contains
CABG and column C contains yes, which of the following formulae will give me
the right answer of 2

=SUMPRODUCT(--(B1:B5=A1), --(C1:C5="Yes"))

OR

=SUMPRODUCT(--(B1:B5="*" & A1 & "*"), --(C1:C5="Yes"))

Additionally what formula would I use if I wanted to know the number of rows
in which both Column B contains exactly CABG and column C contains yes i.e.
the answer would be 1

Thanks

John

"Dave Peterson" wrote:

=countif(b1:b5, "*" & a1 & "*")

JRD wrote:

Is there a way of counting the number of cells in a column that contain at
least in part a certain string variable that is contained in another cell
using for instance the countif function

e.g.
A B
1 CABG CABG
2 CABG
3 PCI
4 CABG, PCI
5 PCI

=countif(b1:b5, a1)

I want the answer to be 3, but the above function gives an answer of 2
because cell B4 does not fit exactly. However, I do want to use a1 as the
criteria and not just put "*CABG*" instead

--

Dave Peterson

Nope.

But you could use:

=sumproduct(--(isnumber(search("cabg",b1:b5))), ...
or
=sumproduct(--(isnumber(search(a1,b1:b5))), ...

=Search() is not case sensitive.
=Find() is case sensitive.



JRD wrote:

Does this formula still hold true if one is using sumproduct e.g.

e.g.
A B C
1 CABG CABG Yes
2 CABG No
3 PCI No
4 CABG, PCI Yes
5 PCI Yes

So if I want to count the number of rows in which both Column B contains
CABG and column C contains yes, which of the following formulae will give me
the right answer of 2

=SUMPRODUCT(--(B1:B5=A1), --(C1:C5="Yes"))

OR

=SUMPRODUCT(--(B1:B5="*" & A1 & "*"), --(C1:C5="Yes"))

Additionally what formula would I use if I wanted to know the number of rows
in which both Column B contains exactly CABG and column C contains yes i.e.
the answer would be 1

Thanks

John

"Dave Peterson" wrote:

=countif(b1:b5, "*" & a1 & "*")

JRD wrote:

Is there a way of counting the number of cells in a column that contain at
least in part a certain string variable that is contained in another cell
using for instance the countif function

e.g.
A B
1 CABG CABG
2 CABG
3 PCI
4 CABG, PCI
5 PCI

=countif(b1:b5, a1)

I want the answer to be 3, but the above function gives an answer of 2
because cell B4 does not fit exactly. However, I do want to use a1 as the
criteria and not just put "*CABG*" instead

--

Dave Peterson


--

Dave Peterson