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