Inserting Blank Cells

Posted by Phil on January 11, 2001 7:28 PM

In the following formula if the value in F5 is not inserted to the cell I want a blank cell to be input.

=IF($A5="","",IF(MONTH(G$3)=MONTH($A5),$F5,""))

If I use the above "" to achieve a blank cell my SUM ARRAY formula returns an #VALUE error (ie. I assume because of the text nature of the "" entry),

{=SUM(($C$4:$C$25="T")*($D$4:$D$25="S")*(G4:G25))}

If I use a zero instead of "" I get an incorrect answer with my COUNT() formula because it counts all numbers including zero.

Is there anyway I can enter a BLANK into cells that will allow my SUM ARRAY & COUNT formulas to work.

Thanks for any help

Phil.

Posted by Celia on January 11, 2001 8:58 PM

=SUM(($C$4:$C$25="T")*($D$4:$D$25="S")*(IF(ISNONTEXT(G4:G25),(G4:G25))))

Celia

Posted by Dave Hawley on January 11, 2001 10:04 PM

Hi Phil

You could use:

=SUM(($C$4:$C$25="T")*($D$4:$D$25="S")*(ISNUMBER(G4:G25)))

To get your count of numeric cells tha meet the criteria

Dave

Posted by Aladin Akyurek on January 12, 2001 2:09 AM

You could have used

=COUNT(Range)-COUNTIF(Range,0)

to cope with zero-values that you don't want to count.

Aladin