If and Sum Nested formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1662112%22%20slang%3D%22en-US%22%3EIf%20and%20Sum%20Nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1662112%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20what%20I%20have%20%3Dif(b9%3AF9%3D0%2C%22%22%2CB9%3AF9).%20Obviously%20it's%20not%20working%20lol.%20I'm%20having%20trouble%20with%20the%20false.%20According%20to%20my%20assignment%2C%20it's%20supposed%20to%20display%20the%20sum%20if%20it%20is%20false.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20how%20the%20assignment%20explains%20it%3A%26nbsp%3BAdd%20formulas%20to%20complete%20the%20table%20of%20hours%20used.%20In%20cell%20B17%2C%20create%20a%20nested%3CBR%20%2F%3Eformula%20with%20the%20IF%20and%20SUM%20functions%20that%20check%20if%20the%20total%20number%20of%20hours%20worked%20in%20week%201%20(cells%20B9%3AF9)%20is%20equal%20to%200.%3CBR%20%2F%3EIf%20it%20is%2C%20the%20cell%20should%20display%20nothing%20(indicated%20with%20two%20quote%20marks%3A%20%22%22).%3CBR%20%2F%3EOtherwise%2C%20the%20cell%20should%20display%20the%20total%20number%20of%20hours%20worked%20in%20week%201.%3CBR%20%2F%3ECopy%20the%20formula%20from%20cell%20B17%20to%20fill%20the%20range%20B18%3AB20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1662112%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1662139%22%20slang%3D%22en-US%22%3ERe%3A%20If%20and%20Sum%20Nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1662139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F792057%22%20target%3D%22_blank%22%3E%40steele86%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20haven't%20used%20the%20SUM%20function%20in%20your%20formula.%20The%20assignment%20says%20to%20check%20the%20total%20of%20B9%3AF9%2C%20so%20where%20should%20you%20use%20SUM%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786837%22%20slang%3D%22en-US%22%3ERe%3A%20If%20and%20Sum%20Nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786837%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bthe%20question%20wants%20us%20to%20create%20a%20nested%20formula%20containing%20IF%20and%20SUM%2C%20so%20we%20are%20asking%20if%20how%20to%20create%20a%20nested%20formula%20containing%20SUM%20and%20IF%20functions%2C%20or%20if%20we%20could%20use%20SUMIF.%20we%20need%20the%20function%20to%20check%20and%20see%20if%20the%20sum%20of%20hours%20worked%20during%201%20week%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%3D0%20if%20yes%20then%20we%20need%20it%20to%20display%20nothing%2C%20otherwise%20we%20need%20the%20cell%20to%20display%20hours%20of%20time%20worked%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786871%22%20slang%3D%22en-US%22%3ERe%3A%20If%20and%20Sum%20Nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786871%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834548%22%20target%3D%22_blank%22%3E%40Oldrockz%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(SUM(B9%3AF9)%3D0%2C%22%22%2CSUM(B9%3AF9))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

5 Replies

@steele86 

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:

=IF(SUM(B9:F9)=0,"",SUM(B9:F9))

 

@steele86 

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

image.png

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

@Riny_van_Eekelen Thank you, this worked!