Count if based on what cell starts with

**Staci** · 03-25-2011, 08:39 PM

I want to know the count of cells that start with "A" in column A
I want to know the count of cells that have a value but does not start with "A" in column A

Would like to know how to do this with a Helper column and without, if it is possible?

See worksheet for more data

Thanks,
Staci

**daddylonglegs** · 03-25-2011, 09:00 PM

You can use "wildcards" with COUNTIF so to count those starting with "A" try

=COUNTIF(A2:A100,"A*")

for non-empty cells that don't begin with "A" you can use SUMPRODUCT, i.e.

=SUMPRODUCT((LEFT(A2:A100)<>"A")*(A2:A100<>""))

for a helper column try this formula in B2 copied down

=IF(LEFT(A2)="A","Red",IF(A1<>"","Blue",""))

**Staci** · 03-26-2011, 08:32 AM

Originally Posted by daddylonglegs

=IF(LEFT(A2)="A","Red",IF(A1<>"","Blue",""))

thinking you meant to say:

Please Login or Register  to view this content.

here...

-thanks spidey everything seems to work

Count if based on what cell starts with

LinkBack

Thread Tools

Rate This Thread

Display

Count if based on what cell starts with

Re: Count if based on what cell starts with

Re: Count if based on what cell starts with

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions