Count If Cell Value Starts with...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scottchampion@gmail.com - 01 Nov 2006 20:34 GMT

I need to count the number of cells in a range whose leftmost character
is an S, or an L.

I attempted to use the formula to count the cells starting with "S":
{=SUMPRODUCT(LEFT(H7:H134,1)="S")}

However it returned a zero, which is not correct. Does anyone know how
I could do this without writing a VB custom function?

Thanks!
Scott

Reply to this Message

Earl Kiosterud - 01 Nov 2006 20:56 GMT

Scott,

The Boolean expression LEFT(H7:H134,1)="S" returns TRUE or FALSE. We need
to coerce that to its equivalent 1 or 0 for SUMPRODUCT to add 'em up. An
easy way is with double negation:

{=SUMPRODUCT(--(LEFT(H7:H134,1)="S"))}

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

>I need to count the number of cells in a range whose leftmost character
> is an S, or an L.
[quoted text clipped - 7 lines]
> Thanks!
> Scott

Reply to this Message

RagDyer - 01 Nov 2006 21:06 GMT

Or simply:

=COUNTIF(H7:H134,"S*")

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Scott,
>
[quoted text clipped - 15 lines]
>> Thanks!
>> Scott

Reply to this Message

Earl Kiosterud - 01 Nov 2006 23:36 GMT

RagDyer,

No. Too easy!

Signature

Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------

> Or simply:
>
[quoted text clipped - 19 lines]
>>> Thanks!
>>> Scott

Reply to this Message

Ron Coderre - 01 Nov 2006 23:45 GMT

Left character is either S or L, right?

Here you go...

=SUM(COUNTIF(A1:A10,{"S*","L*"}))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

> I need to count the number of cells in a range whose leftmost character
> is an S, or an L.
[quoted text clipped - 7 lines]
> Thanks!
> Scott

Reply to this Message

Rate this thread: