If and Sum Nested formula

Occasional Visitor

This is what I have =if(b9:F9=0,"",B9:F9). Obviously it's not working lol. I'm having trouble with the false. According to my assignment, it's supposed to display the sum if it is false.


here is how the assignment explains it: Add formulas to complete the table of hours used. In cell B17, create a nested
formula with the IF and SUM functions that check if the total number of hours worked in week 1 (cells B9:F9) is equal to 0.
If it is, the cell should display nothing (indicated with two quote marks: "").
Otherwise, the cell should display the total number of hours worked in week 1.
Copy the formula from cell B17 to fill the range B18:B20.

9 Replies


You haven't used the SUM function in your formula. The assignment says to check the total of B9:F9, so where should you use SUM?

@Hans Vogelaar the question wants us to create a nested formula containing IF and SUM, so we are asking if how to create a nested formula containing SUM and IF functions, or if we could use SUMIF. we need the function to check and see if the sum of hours worked during 1 week is 

=0 if yes then we need it to display nothing, otherwise we need the cell to display hours of time worked 

@Oldrockz Try this:




You may use SUM() or whatever function directly without checking the condition by IF(), just add two semicolons in custom format for such cells


Same works with other formats, it shall be nothing after the second semicolon.

@Riny_van_Eekelen Thank you, this worked!

@Riny_van_Eekelen Perfect, thank you

Thank you! I also am working on this assignment for Excel and could not figure out this formula either. I greatly appreciate your help!


I have a above formula in column but I need a Sum of this column ang SUM formula is showing 00:00



The values in column F are left-aligned. That suggests they might be text values.

Try the following:

  • Select column F.
  • Set the number format to a time format,
  • On the Data tab of the ribbon, click Text to Columns.
  • Click Finish.

Does that make a difference?