Sumproduct function to return blank instead of 0

Excel Gurus please help -

I'm using a if(sumproduct) function on my workbook which works fine. But the only problem that I have is I want the formula to return "blank" instead of $0.00.

Can someone please help me solve this mystery. My formula is given below -

=IF($P19="BE",SUMPRODUCT((Work_Num=$O19)*(Wk_Ending_Dt=V$7)*(Total_Hours)*(Billing_Rate)),SUMPRODUCT((Work_Num=$O19)*(Wk_Ending_Dt=V$7)*(Total_Hours)*(Avg_Rate)))/1000000

Regards,
Sunil

Hi Sunil,

Go to the number formatting, if you are using currency, choose the Category: Custom

This should appear in Type: _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Change it to this instead... _($* #,##0.00_);_($* (#,##0.00);;

Hi djapigo,

I tried using the custom format that you suggested but I get a space between the $ and amount like this - $ 0.30. There should not be any space between the $ and the amount. I tried playing around with it but could not get it to work. Can you please help me fix this?

Try this...
_($#,##0.00_);_($(#,##0.00);;

Remove the * and 'space' on both (note: "* space" means repeat the space until the whole cell is filled in)

Quick lesson: number formatting has 4 types (separated by semicolon) positive;negative;zero;text
Each type is the format of how you want it to look like... (see number formatting in the help section)

The reason why 0's are not showing up is that we "removed" the format... hence the 2 semicolons at the end...

Hope this helps...

Fantastic djapigo. I'll surely spend some time tonight on the number formatting in the help section.

Thanks so much.

No problem... glad to help...

daddylonglegs,

I really like your "force an error for zero"... very clever!