One of the advantages with Excel is that you can usually handle exceptions. In this post I examine a way to handle exceptions or adjustments without using the IF function.

In a recent project I needed to allow the user to make some adjustments. In some cases, the user needs to add cost centres to a total and in another they needed to remove cost centres. I handled this requirement with two separate SUMIFS formulas. One for the addition and one for the subtraction.

These were straight forward types of adjustments, but I wondered could I create a single formula to handle both additions and subtractions without using an IF function?

I came up with the solution below (I didn’t use this on the project as it has the issue that I will explain below).

On the left of the sheet there is a simple table with states, regions, and amounts – see image below.

The report on the right summaries this table by the region in cell F2. The user can enter any state in cell F4 and F5 and choose to add or subtract that state from the region total.

In the image above ACT has been removed from the East region and QLD has been added.

The formula in cell G4 has been copied to G5 and doesn’t contain an IF function.

One formula handles both adding or subtracting a value.

The user enters a state followed by + or – this determines whether the state is added or subtracted.

You need to place the + or – at the end, otherwise Excel will try to interpret the state as a range name and cause a #NAME? error – see image below.

The secret to the formula in cell G4 is the use of the RIGHT function.

=(RIGHT(F4)&1)*SUMIFS($C$2:$C$9,$A$2:$A$9,LEFT(F4,LEN(F4)-1))

The RIGHT function extracts the last character from cell F4 and we then join that character with a 1 using the & symbol. The & symbol joins text together. We end up with either +1 or -1. But this is text. Because we multiply the text result by a SUMIFS function, Excel converts the text +1 and -1 into values. This correctly adjusts the SUMIFS function for the required state adjustment.

The last argument in the SUMIFS function removes the last character from cell F4 for the state calculation.

The problem

Unfortunately, the user has the power to adjust by any state. This could lead to doubling up of a state already in the selected region or, deducting a state that wasn’t in the selected region.

So, I achieved my goal, but the solution isn’t robust enough to use in practice.

This technique might have other applications, but in this case two separate adjustment formulas is a better solution than one.

I will share the two formula solution in the next post.