Monday Morning Mulling: March 2018
2 April 2018
On the last Friday of each month we set an Excel puzzle for you to puzzle over the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.
Welcome back – if you’re lucky enough to have had a long weekend, then we hope you’ve enjoyed the extra time to puzzle over our Friday Fix challenge!
Let’s recap our problem from last Friday.
March’s challenge was about creating a single formula to sum the sales amounts based on the values in the Business Unit and Quarter columns that is also able to use blank values as a criterion.
Let’s try using the SUMIFS function:
=SUMIFS(D5:D10,B5:B10,H5,C5:C10,H6)
The SUMIFS function is unable to pick up blank values as a criterion. With the current input we would expect a total sales value of 50.
So how do we get SUMIF to recognize blank values? We can use “” as the criterion:
=SUMIFS(D5:D10,B5:B10,H5,C5:C10,"")
Now we have to consider the different input combinations we can have. For instance, a blank value in Business Unit and ‘3’ in Quarter. Hard coding for all the criteria will not work for all combinations.
We have to incorporate the IF function into our formula to allow for all possible input combinations:
=IF(AND(H5="",H6=""),SUMIFS(D5:D10,B5:B10,"",C5:C10,""),IF(H5="",SUMIFS(D5:D10,B5:B10,"",C5:C10,H6),IF(H6="",SUMIFS(D5:D10,B5:B10,H5,C5:C10,""),SUMIFS(D5:D10,B5:B10,H5,C5:C10,H6))))
This solution is very long and contains 3 nested IF statements, which not only greatly increases the chance of an error but is confusing to read. Furthermore, what happens when we add another column? We would need to have 7 nested IF statements for the formula to work.
Clearly this is not the right path to take.
The Solution
We can use the ‘&’ operator to concatenate the blank value and the input cell into the criteria:
=SUMIFS(D5:D10,B5:B10,""&H5,C5:C10,""&H6)
In this case incorporating the ‘&’ operator into the SUMIFS function allows us to use blanks values as a criterion and allows for easy scaling if we wish to include even more columns:
=SUMIFS(E5:E10,B5:B10,""&I5,C5:C10,""&I6,D5:D10,""&I7)
There you have it, our solution to use blanks as a criterion with the option to use multiple criterion.
You can find the solution file here.
Join us again on the last Friday of April for our next Final Friday Fix! In the meantime you can tune in to our blog page for more content every Monday to Friday!
