Ignore blank celss in left function

[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

 

[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. Valko]

Try it like this (array entered):

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