Ignore blank celss in left function

B

bradmcq

Hi,

I am using a left function as part of an array formula to count cells
starting with 1.1
I have used the left formula to recoginse 1.1 as a number, as they are
entered in the cells as numbers.

The formula works accept when the cell range contains a blank cell or text.
I want to be able to refernce a column that might contain blank cells or
cells that contain text. Essentially I want the left funtion to ignore these
cells or recoginise them as 0 instead. At the moment the left formula
returns a #VALUE error for these cells.

Is there any way to have the left function recognise numbers as numbers and
ignore blank celss or cells with text.

My formula is
=SUM(IF(((A1:A7="Completed")*(B1:B7="Q1")*(--LEFT(C1:C7,3)=1.1))>0,1,0))
Thanks
 
R

Ragdyer

Try this:

=SUMPRODUCT((A1:A7="Completed")*(B1:B7="Q1")*(ISNUMBER(FIND("1.1",LEFT(C1:C7
,3)))))

Not an array formula like yours - just regular entry!
 
T

T. Valko

Try it like this (array entered):

=SUM((A1:A7="Completed")*(B1:B7="Q1")*(LEFT(C1:C7,3)="1.1"))
 

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

Top