This blog and video will demonstrate two alternative ways, using Excel SUMIFS function, to sum a range ignoring blank, text or error cells.

The Excel SUMPRODUCT function is one of the most powerful and flexible aggregation functions. This example demonstrates how SUMPRODUCT can discriminantly sum cells in either column H (“Invoice Amount”) or column I (“Amount Confirmed”), based on whether there is a corresponding amount in column I.

A standard Excel VLOOKUP can refer to values from a specific worksheet. Financial and business planning models are often complex and detailed in their analytics, and therefore require high-level executive summaries or dashboards of multiple worksheets.
The following illustration demonstrates the power of a VLOOKUP with the INDIRECT function to reference a Lookup_value from multiple worksheets.