SumIFS beats SumIF
You need a data amalgamation solution and SumIF is just fine. Amalgamating data is what SumIF is there for.
It would be easy to gravitate towards SumIFS instead though. SumIF will total data based on one constraint. SumIFS will amalgamate when given multiple constraints and will also filter data based on one constraint. That means SumIFS does exactly what SumIF does but more.
If you think about it, SumIF has no reason to live
This all makes me wonder how SumIF manages to live with itself. What's its reason for being? How does it manage to get out of bed in the morning? SumIFS can do what SumIF does plus more. SumIf has no reason to be alive.
SumIFS can amalgamate using multiple constraints. But life must seem even tougher for SumIF when it thinks about what Sumproduct can do (amalgamate over multiple?constraints in multiple dimensions).
Comparing SumIF and SumIFS side by side
The simple data amalgamation exercise below requires you to:
Immediately at the first stage SumIF starts to struggle. Because SumIF can only amalgamate by one criterion (either quarter or year) you need two different SumIF functions at the first stage. You need two different SumIF formulas as you go from column E (year 1) to column F (year 2).
领英推荐
SumIFS beats SumIF every time
SumIFS can amalgamate data using multiple criteria (quarter and year in this case) which makes it a much better solution at the first stage of the exercise.
SumIFS can also quite happily amalgamate using one criterion (subsidiary at stage 2 of the exercise). That really really makes you wonder why SumIF even exists. SumIFS can do what SumIF does (one criterion) but more (multiple criteria).
Sumproduct beats them both
See this separate article on Sumproduct's superpower. Here's Sumproduct totalling besting both SumIF and SumIFS in today's example.
SumIF is already feeling inadequate against SumIFS but life gets even worse for SumIF when it?compares itself to Sumproduct.
SumIF is not flash and just does what it says on the tin
SumIF survives perhaps by reconciling itself to its position in life. It knows it can only do one job. It just gets on with it. If SumIF were appearing on TV's Masterchef it might be appearing as a humble tin of baked beans. We all like baked beans.