Sum IFs question

Copper Contributor

I'm building a spread sheet at work to calculate the sum of weights.   Column B has all of our "truck loads" weights that contain NO brick.  Column B has all of the  "Truck Loads" Total weight (WITH brick). 

I have a simple Sum Formula for Column A ( Just adds up all of the weights in Column A)  But I need to come up with a Formula that Calculates the weights of All of the loads.  The issue is,  Some Trucks may be partially Brick loads along with other material.  Other Loads May not have any Brick.  Or some loads may have all Brick.   The issue comes into play when a "truck load" has no brick.  If I did a simple Sum formula for Column B,  It wouldn't look at those loads in Column A that contain no brick.  I need a Formula (I'm guessing an intricate SUMIF formula)  That looks at all of Column B but if a cell is blank It looks at the value in Column A and adds that instead.  Any Ideas?  anyones help is much appreciated (I've attached what I have so far.   Formula I'm looking for would go into the bottom where I've highlighted in Red (Total weight With Brick)

2 Replies

@mdmiles1485 

 

I think you want  =SUM(IF(E6:E20="",C6:C20,E6:E20))

If you do not have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

@mdmiles1485 

If as regular formula without CSE

=SUM($E$6:$E$20) +
    SUMPRODUCT(
        $C$6:$C$20 * ($C$6:$C$20 > $E$6:$E$20)
    )