IF/OR formula for column

GabyC

Hello,

I'm looking for a formula that I can apply to the whole column with the following logic entered in each of the blue columns.

If the "Month" column if "January, then fill in the blue cells with correct months information on the right. Ideally, the first row of blue cells should match the 3 purple cells to the right. Then I need to have it written for all the months. SO if "month" if February, then input the February data.

Frank S.

Greetings @GabyC

You are looking at a large nested IF statement, and it might look something like this:

Requested Month Budget

=IF(Month@row = "January", [Jan 23 Budget]@row, IF(Month@row = "February", [Feb 23 Budget]@row, "")) continue if statements for each month.

Requested Month Spent to Date

=IF(Month@row = "January", [Jan 23 Spent to Date]@row, IF(Month@row = "February", [Feb 23 Spent to Date]@row, "")) continue if statements for each month.

Use the same process for the next column.

I hope this helps, and let me know if you have any additional questions.

Brent Wilson

@GabyC It is doable but can be a challenge with dates As "January" does not equal "Jan" or Jan23

Also going from Rows to Columns will require you to do an Index Match and the match needs to be exact. Example January = January

You might want to have a quick think about restructuring your data into two tables before you get further.

It might be good to have a separate table with the budget numbers

Then you can do a simple VLOOKUP on January in this table and have your formulas for the budget reside here.

This way you can also rename your Jan 23 Budget to "January"

I have also created a cheater table in the past to lookup for months which may help

Remember you can try and match it via the text "March=March" or using dates such as IF(Month([Date]@row)=3,A,B)

Matching Dates is much harder but then you can do the math on it to add a month if you need that

Also, the requested month delta can be calculated once you have the correct Budget and requested Month Spend selected. no need to go through the effort of looking that up.

Hope that gives you some food for thought.

If you think of it like a database why would you have your budget in your actuals or request file.. they would be in two separate locations..

Let me know and feel free to ask more

Frank S.

Greetings @GabyC

You are looking at a large nested IF statement, and it might look something like this:

Requested Month Budget

=IF(Month@row = "January", [Jan 23 Budget]@row, IF(Month@row = "February", [Feb 23 Budget]@row, "")) continue if statements for each month.

Requested Month Spent to Date

=IF(Month@row = "January", [Jan 23 Spent to Date]@row, IF(Month@row = "February", [Feb 23 Spent to Date]@row, "")) continue if statements for each month.

Use the same process for the next column.

I hope this helps, and let me know if you have any additional questions.

GabyC

@Frank S. Thank you this is exactly what I was looking for!

GabyC

@Frank S. - I have another question for you. The formula that you provided works great by the way!

Can you help me with a formula for "Jan 23 Spent to Date" column that reads to the following logic?

If "Budget Type" is "Estimate" and the "Month" is "January" sum the "Requested Amount" column BUT if the "Budget Type" is changed to "Actual" only add the "Actual Spent" column to the "Jan 23 Spent to Date" column.

Does that logic make sense to you?

Thank you!

Frank S.

@GabyC

Good morning.

I'm going to write what I think you are looking for and you can confirm:

If Budget Type is Esitmate and Month is January, Sum the Requested Amount. What column do you want the SUM amount to appear in? Does the user enter in the Requested Amount? I need to understand where the numbers we are SUMing come from. I created a "Requested amount Total" column to track the SUM of the requested amounts.

Here is a formula example for Requested Amount: SUMIFS([Requested Amount]:[Requested Amount], [Budget Type]:[Budget Type], "Estimate", Month:Month, "January")

Here is a formula example for Actual Spent: SUMIFS([Actual Spent]:[Actual Spent], [Budget Type]:[Budget Type], "Actual", Month:Month, "January")

Let me know if this is heading in the right direction.

GabyC

The column we want the sum to appear in is "Jan 23 Spent to Date".

Maybe if I explain it like this.

If the "Budget Type" is "Estimate" AND the "Month" is "January" AND "Director approval" is either "Approved or Submitted" add the "Requested Amount" to "Jan 23 Spent to Date".

I have a workflow set up where when our accountant adds the "Actual Amount" to the line item, the "Budget Type" column changes to "Actual". So when it is changed to "Actual" I want the cost that is typed in the "Actual Spent" column to over ride the requested amount in the formula that is summing in the "Jan 23 Spent to Date" column.

So it would be the first formula + BUT if there is a cost entered in "Actual Spent" column, add that cost to the total in "Jan 23 Spent to Date" column.

Let me know what you think.

With the data entered below, ideally the number in the Jan 23 Spent to Date column would read $1,009.