# SUMIF Challenging Formula

Options
✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Try something like this...

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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 = "")

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭
Options

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...

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

@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?

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!