mardi 27 février 2018

Excel function to sum all actual costs plus estimates on blank rows with no calculation column

In Excel 2013, I need to sum all numbers in a column of actual costs plus estimates in a second column in rows where the actual costs are blank.

                   Estimate (A)    Actual (B)
     Row 1             106                  
     Row 2             212            230
     Row 3             318            295
     Row 4             424                
     totals            1060           525

I need to return 106 + 230 + 295 + 424. (or 525 + 106 + 424)

What I have tried:

--I have solved the problem if I put a placeholder (like "missing") in the blanks and then using a SUMIF nested in a simple SUM. But that badly clutters the chart. =SUM(A5, SUMIF(B1:B4,"missing",A1:A4))

--I have also solved the problem by creating a calculation column that has an ISBLANK function and then using the SUMIF over that result. However, I can't figure out how to consolidate. I realize I could create another sheet to hold the calculation column, but the workbook will already have a number of sheets and I want to avoid an extra. C1=ISBLANK(B1) dragged down to C4 and then =SUM(A5, SUMIF(C1:C4, "TRUE", A1:A4))

--I have found a number of online descriptions of managing similar tasks with pivot tables and months, but I can't seem to figure it out for a simple table.

I think my ISBLANK attempt is failing on consolidation because of something to do with absolute references vs. ranges in the column, but I can't figure it out.

Any advice would be greatly appreciated--thanks

Aucun commentaire:

Enregistrer un commentaire