This article and video will demonstrate two alternative approaches, using Excel SUMIFS function, to sum a range and ignore blank, text or error cells. Although there are a myriad of other solutions, this article will focus solely on these two examples of the formula.
There are many situations, where financial model users require a provisional total of a range; even if there are erroneous cells in a range such as blank, text or error cells.
This article and video will demonstrate two alternative approaches, using Excel SUMIFS function, to sum a range and ignore blank, text or error cells.
The key considerations for using this Excel function was the importance of:
- Ignoring blank cells,
- Overlooking text cells, and
- Discounting error cells in a sum range.
Excel SUMIFS Function – 1st Approach
This formula will be able to sum and ignore blank, text and error cells. It will apply the following formula, which must be accurately entered; otherwise the function will not return the correct value.
As per the below video, copy the following Excel SUMIFS formula:
=SUMIFS($H$5:$H$51,$B$5:$B$51,”<>”,$H$5:$H$51,{“>0″,”<0”})
Excel SUMIFS Function – 2nd Approach
Once again the same Excel function is applied. The key difference is the use of “<“&99^99 for Criteria 2, instead of the {“>0″,”<0”} input value, which was applied in the above 1st Approach.
Copy the following Excel SUMIFS formula, which was demonstrated in the video:
=SUMIFS($H$5:$H$51,$B$5:$B$51,”<>”,$H$5:$H$51,{“>0″,”<0”})
Video and workbook for the example
The following Excel spreadsheet and YouTube video illustrate the use of this Excel function.
Sum range across blank, text or error cells – Strategize Financial Modelling.xlsx
