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
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