Ignore blank celss in left function
 

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

Ignore blank celss in left function
 

Try this:

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

Not an array formula like yours - just regular entry!

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"bradmcq" wrote in message
...
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

Ignore blank celss in left function
 

Try it like this (array entered):

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

--
Biff
Microsoft Excel MVP


"bradmcq" wrote in message
...
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