Hi - Dave here.
Happy Friday!
The SUMIFs function is useful for all kinds of things, but there are some "simple" problems it just can't solve.
I ran into this recently in the worksheet below. The goal is to calculate a sum for each group (A, B, and C) across all three months of data in the range C5:E16. In other words, we want to perform a "SUMIF" with data in three columns.
You might think this is a perfect fit for SUMIFS, but no. The formula below returns a #VALUE! error. The problem is that SUMIFS requires the
sum range to be the same size as the
criteria range.
[
Download the workbook and read the full explanation]
I know two good formulas to solve this problem. The first is with FILTER + SUM, and the second is with SUMPRODUCT + Boolean algebra. Both formulas can be customized to use more advanced criteria. Read the article above for the details, then download the worksheet and try it yourself.
Note: The FILTER solution requires Excel 2021 or later. The SUMPRODUCT solution will work in any Excel version.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly. Each Friday, I explain how to solve a problem with an Excel formula. You can sign up on our home page.