Sign up to hear about free Excel training.
I won't share your email with anyone.
2 mins read
In the image below we want to total up the amount column when the state equals WA and the department is sales.
In this case that is just a single line but if there is multi month data in the table then this would add up the combination.
The formula in cell G3 is a standard SUMIFS function for these two criteria.
What if we delete the state in cell G1?. In the image below you can see that the total in cell G3 automatically changes to 0.
What if we wanted the calculation to change so that it added up all the sales department’s values regardless of the state?
Alternatively, we may delete the department and add up all of the values for a state.
We can handle both these situations by amending the SUMIFS function.
The image below has the amended SUMIFS function in cell G4 which handles the situation when either the state or the department or both are deleted.
=SUMIFS(D2:D13,B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2))Note this technique only works with text-based codes. The asterisks symbol is a wildcard symbol that represents all values, but it doesn’t work with numeric codes.
The IF functions return the asterisks symbol if the input cell is blank. The asterisks symbol represents all entries. If the input cell is not blank, then the input cell value is used.