Reasons why SUMPRODUCT is better than SumIFs/CountIFs
SumIF(s) and CountIF(s) have been used for years by thousands of Excel users. They are useful formulas, however SUMPRODUCT is simply a more versatile and flexible formula to use in all aspects.
Tutorials for the three formulas can be found at te bottom of this page.
Nested Formulas
The formula on the left includes three filters, the “Disbursement Date” must be in the year 2020, the “Closing Date” has to be longer than two (2) characters long, and the “Loan Folder” cannot include the name “Test”. The formula will sum the “Total Loan Amount” for all records that include these filters. SumIF/CountIF can not include nested formulas like we have done here. This SUMPRODUCT formula has pulled off a complicated task, with minimal effort.
‘OR’ Condition
The formula on the left includes three filters. It is summing the “Total Loan Amount” if ANY of the three filters are true. You can do this by using the ‘+’ sign between the filters. SumIF/CountIF can achieve similar results, but it requires quite a bit more work, and can be cumbersome.
Functionality
SUMPRODUCT can achieve results that SumIF/CountIF simply can’t. The formulas to the left can only be completed with the SUMPRODUCT formula.
