counting Charactors

**jgreger** · 04-28-2009, 03:00 PM

How do you write a function to count the number of times the name is in this list? I'd like it to return 13-

BALTHAZOR, JESSE C37442927
BALTHAZOR, JESSE C37523578
BALTHAZOR, JESSE C37552945
BALTHAZOR, JESSE C37564610
BALTHAZOR, JESSE C37570239
BALTHAZOR, JESSE N37518673
BALTHAZOR, JESSE N37519002
BALTHAZOR, JESSE N37547983
BALTHAZOR, JESSE N37550370
BALTHAZOR, JESSE N37554772
BALTHAZOR, JESSE N37565060
BALTHAZOR, JESSE N37569185
BALTHAZOR, JESSE T37440568

**shg** · 04-28-2009, 03:17 PM

=countif(a1:a13, "BALTHAZOR, JESSE *")

**jgreger** · 04-28-2009, 05:18 PM

great that worked perfectly-

one more question-

Now that I've counted the names- I want a function that will count the next column type-

So I'm getting 13 for how many Balthazor's - now of the 13 how many start with a C?
BALTHAZOR, JESSE C37442927
BALTHAZOR, JESSE C37523578
BALTHAZOR, JESSE C37552945
BALTHAZOR, JESSE C37564610
BALTHAZOR, JESSE C37570239
BALTHAZOR, JESSE N37518673
BALTHAZOR, JESSE N37519002
BALTHAZOR, JESSE N37547983
BALTHAZOR, JESSE N37550370
BALTHAZOR, JESSE N37554772
BALTHAZOR, JESSE N37565060
BALTHAZOR, JESSE N37569185
BALTHAZOR, JESSE T37440568

I have =COUNTIF(Sheet3!A:A, "BALTHAZOR, JESSE") then I want to add of the ones that say Balthazor, Jesse how many start with a C

Thanks for all the great help

**shg** · 04-28-2009, 05:22 PM

C'mon, now: =countif(a1:a13, "BALTHAZOR, JESSE C*")

**jgreger** · 04-28-2009, 05:46 PM

That works if the data was all in one column. they are in 2 columns

So I need to look at both column A and B or a way to merge the two

**TonyS** · 04-28-2009, 06:51 PM

=sumproduct(isnumber(search("balthazor, jesse",a1:a13))*(left(b1:b13,1)="c"))

**shg** · 04-28-2009, 06:56 PM

That works if the data was all in one column. they are in 2 columns

That's why we prefer people post workbooks.

If the entire name columns contains only names with no trailing (or leading) spaces, then

=sumproduct( (a1:a13="balthazor, jesse") * (left(b1:b13,1)="c") )

And in that case, the first formula can be =countif(a1:a13, "BALTHAZOR, JESSE"), which is must faster than a wildcard comparison.