Count If Cell Value Starts with...

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
 
E

Earl Kiosterud

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"))}
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to count cells in Column1 where each cell's row also hasspecified value in Column2 1
Empty Cells 1
Count Unique Cells, within a date range (dates stored in separatecolumn). 3
Excel Use COUNTIF across a range of cells with formulas 0
Count Dates? 5
Counting Consecutive cells in a row with a value greater than zero - how? 4
is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ? 5
Odd problem with formula counting Yes and No 5

Top