SUMIF Challenging Formula

Susan Swisher

Here is a tough one:

Look in Column (Budget Requested) [this will always have a $$ amount in it]

THEN

Look in Column (Budget Approved) [this might have a $$ amount in it]

IF

Column (Budget Approved) has $$ amount, use that number and SUM it; however, if Column (Budget Approved) is blank, SUM (Budget Requested)

Paul Newcome

Ok. So having the extra columns and criteria is going to change it. We have to include those extra columns and criteria into step 1 which turns the initial SUM into a SUMIFS.

Based on the formula you just posted which includes all of the extras, try this...

=SUMIFS([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], [INCY Compound]:[INCY Compound], "INCB024360", Approved:Approved, 1) + SUMIFS([Budget Requested (INCYTE)]:[Budget Requested (INCYTE)], [Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], @cell = "", [INCY Compound]:[INCY Compound], "INCB024360", Approved:Approved, 1)

Susan Swisher

I knew I was messing it up... I left our those columns/criteria, thinking I could just string them onto the end... AS soon as I used your formula... BOOM (it worked)... YOU @Paul Newcome are a ROCKSTAR!

Thank you for all your help, this afternoon

Paul Newcome

Try something like this...

=SUM([Budget Approved]:[Budget Approved]) + SUMIFS([Budget Requested]:[Budget Requested], [Budget Approved]:[Budget Approved], @cell = "")

Susan Swisher

It worked.. however, I might have been clear; because it is totally BOTH columns. It has to count the "Approved Budget" (if that has a $$), then, if the "Approved Budget" is blank, it will count the "Budget Requested" and THEN ADD those 2 figures together

Paul Newcome

That is exactly what my formula is doing.

First we sum the approved column (blanks equal zero which doesn't affect the total).

SUM([Budget Approved]:[Budget Approved])

Then we sum the requested column for only those rows where the approved column is blank.

SUMIFS([Budget Requested]:[Budget Requested], [Budget Approved]:[Budget Approved], @cell = "")

Then we add the two together.

=SUM([Budget Approved]:[Budget Approved]) + SUMIFS([Budget Requested]:[Budget Requested], [Budget Approved]:[Budget Approved], @cell = "")

Susan Swisher

The problem is..... both columns have data in it; and it is returning the total of the two columns; unless I am doing something wrong. We can't add the two together, only pick up the number, from the "Budget Requested", IF the "Budget Approved" is Blank (because we haven't approved it yet)

Eid E. Eid

Here is my 2 cents ( I am not a heavy user, so this could be totally dumb...)

Create a third column "Budget to SUM", and for each row put in it =IF([BUdget Approved]<>0,[Budget Approved],[Budget Requested])

Then sum this third column...

Paul Newcome

I set up a sheet to mirror your screenshot to include column names and dollar amounts, and the formula worked for me.

The only difference is that the column names I used in the formula posted above are different because I was going by what you had in your original post, but the formula does work as intended.

Paul Newcome

@Eid E. Eid That should also work, but it requires an extra column. The formula provided above should be working. I have even tested it and found it to be accurate.

@Susan Swisher Where exactly are you putting the formula?

Susan Swisher

Interesting; I have an additional factor (in the formula); so that must be messing it up; I needed it to look in another column (to = something) and another column to make sure the box was checked... that must be throwing it off:

=SUM([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)]) + SUMIFS([Budget Requested (INCYTE)]:[Budget Requested (INCYTE)], [Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], @cell = "", [INCY Compound]:[INCY Compound], "INCB024360", Approved:Approved, 1)

Susan Swisher

@Paul Newcome I am putting it in the Sheet Summary, because I am creating a report to use metrics/charts in a Dashboard

Paul Newcome

Ok. So having the extra columns and criteria is going to change it. We have to include those extra columns and criteria into step 1 which turns the initial SUM into a SUMIFS.

Based on the formula you just posted which includes all of the extras, try this...

=SUMIFS([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], [INCY Compound]:[INCY Compound], "INCB024360", Approved:Approved, 1) + SUMIFS([Budget Requested (INCYTE)]:[Budget Requested (INCYTE)], [Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], @cell = "", [INCY Compound]:[INCY Compound], "INCB024360", Approved:Approved, 1)

Susan Swisher

I knew I was messing it up... I left our those columns/criteria, thinking I could just string them onto the end... AS soon as I used your formula... BOOM (it worked)... YOU @Paul Newcome are a ROCKSTAR!

Thank you for all your help, this afternoon

Paul Newcome

Glad we got it working. 👍️

Please don't forget to mark the most appropriate response(s) as "helpful". This will let others searching for a similar solution know that one may be found here.