Hi,
I am trying to use a sumifs formula to pull back data based on multiple column criteria and a single row criteria but my formula just returns #value. I have attached a sample of the workbook with my formula in the green cell.
I could use sumproduct which I can get to work, but some sheets have over 4000 rows and the number of cells with formulas takes ages to calculate and I understand sumifs are far more efficient in their use of memory resources.
Any help greatly appreciated.
Thanks
Martin
SUMIFS won't work for that, you will need SUMPRODUCT. It won't be that much less efficient assuming you only refer to the necessary ranges and not entire columns.
That explains why I couldn't get it to work.
Many thanks for taking the time to respond
Hello,
I know you already got a sufficient answer, just want to add that you can use an array formula with SUM to get what you want. More about array formulas here in this blog post.
If you have Excel 365 then you don't need to finish off the array formulas with Ctrl+Shift+Return any longer, but if you or someone else who is to use this file have an older version then you must press Ctrl+Shift+Return to get the array formula working. See attached file for examples.
Br,
Anders
