Count If Cell Value Starts with...

  • Thread starter Thread starter scottchampion
  • Start date Start date
S

scottchampion

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
 
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"))}
 
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
 
Back
Top