Hi, in Excel there are many roads to achieve one result. But you can't learn them all. So I think it makes sense to select for certain purposes one way and to stick with it to develop a better feeling and quicker automatism.
Sumif and Sumifs have a different structure, what about only using Sumifs even for a single condition?
-only one formula structure to learn
-you never know if you need to bring in a second condition
Sumproduct can do even more then Sumifs, what about only using Sumproduct?
-only one formula structure to learn
-you never know if you need to bring in also OR conditions
I am not arguing against the freedom of choice, I would just like to know if there are hidden disadvantages in using the formulas which look to me on first sight as superior.
Hi Matthias,
You raise a good point and if you're the only one using the Excel workbook then you should use whatever is more efficient for you. However, if you're sharing your workbook then you need to consider backward compatibility, e.g. SUMIFS isn't available in Excel 2007, likewise COUNTIFS. And the abilities of those you're sharing your file with.
e.g. SUMIF/S written with SUMPRODUCT is a much more difficult formula for users to write and understand. If your colleagues aren't comfortable with SUMPRODUCT then it's better to use something more user-friendly.
Lastly, I think it's better for you to be familiar with and confident with many approaches, you never know when you might change jobs and need to use a different function for the above reasons cited.
Kind regards,
Mynda
Hi Mynda,
Sumproduct is in indeed a more difficult formula and that is why I thought to switch to it, to get more routine. But your point about with whom I share my workbooks is valid.
Sumifs were introduced 2007, so in a working environment that should be ok. But me too, I was very surprised when I saw the first time Sumifs and Sumproduct in one of "my" workbooks introduced by colleagues. Sumifs I understood, but the * from Sumproduct I completely misunderstood.
Thanks,
Matthias
