IF/OR Formula Issue with writing

Lydia Bowman

Hi!

I'm trying to figure out a formula for an IF/OR situation. Basically -- if the [Add-ons] column includes the tag "Prebill," I want the [Final Invoicing Amount] column to be "[Total Project Cost] - [Prebill Actual Invoiced]." If it's NOT tagged "Prebill," I want the [Final Invoicing Amount] column to be "[Total Project Cost] - [FD Actual Invoiced]".

I have a formula that works for the first scenario (not written as an IF formula), but I don't know how to make it into an IF formula that will return the other SUM in the OR situation. Here's the formula I have so far (that works): =SUM([Add-Ons]@row, HAS(@cell, "Prebill"), SUM([Total Project Cost]@row - [Prebill Actual Invoiced]@row))

And this is the formula I want it to perform if [Add-ons]@row does NOT have "Prebill": =SUM([Total Project Cost]@row - [FD Actual Invoiced]@row)

So, in theory it should be something like below, but this isn't working: =SUMIF(([Add-Ons]@row, HAS(@cell, "Prebill"), SUM([Total Project Cost]@row - [Prebill Actual Invoiced]@row)), SUM([Total Project Cost]@row - [FD Actual Invoiced]@row))

Any thoughts or suggestions are appreciated!

Nick Korna

Hi @Lydia Bowman,

If your projects are being displayed on a single row, then you could use something like this:

=IF(HAS([Add-ons]@row, "Prebill"), [Total Project Cost]@row - [Prebill Actual Invoiced]@row, [Total Project Cost]@row - [FD Actual Invoiced]@row)

Example output:

If your sheet is somewhat different, is it possible to post an screenshot (obscuring any sensitive data) as an example to work from?

Nick Korna

Hi @Lydia Bowman,

If I've understood you correctly:

=IF(AND(HAS([Add-ons]@row, "Prebill"), [Final Invoicing]@row = "Invoiced"), [Prebill Actual Invoiced]@row + [Final Invoicing Amount]@row, IF(HAS([Add-ons]@row, "Prebill"), [Prebill Actual Invoiced]@row + IF([Final Invoicing]@row = "Invoiced", [Final Invoicing Amount]@row, 0), SUMIF([First Draft Invoicing]@row, "Invoiced", [FD Actual Invoiced]@row) + SUMIF([Final Invoicing]@row, "Invoiced", [Final Invoicing Amount]@row)))

Sample output (probably with some scenarios that shouldn't happen around pre-bill invoicing):

Hope this helps, but if I've got something mixed up then just let us know!

Nick Korna

Hi @Lydia Bowman,

If your projects are being displayed on a single row, then you could use something like this:

=IF(HAS([Add-ons]@row, "Prebill"), [Total Project Cost]@row - [Prebill Actual Invoiced]@row, [Total Project Cost]@row - [FD Actual Invoiced]@row)

Example output:

If your sheet is somewhat different, is it possible to post an screenshot (obscuring any sensitive data) as an example to work from?

Lydia Bowman

Thank you @Nick Korna! I'll give it a shot and keep you posted on how it works out.

Lydia Bowman

Thanks again, @Nick Korna — the formula worked! We greatly appreciate it.

Lydia Bowman

Hi @Nick Korna,

We have a follow up question in this thread/ another formula I'm struggling to put into a single IF/OR/AND formula.

Basically, if the [Add-ons] column includes the tag "Prebill" AND if the [Final Invoicing] column is “Invoiced”, I want the [Total Amount Invoiced] column to add [Prebill Actual Invoiced] and [Final Invoicing Amount]. To clarify, if just one of these true, I'd still want the [Total Amount Invoiced] to populate with the amount of the true value. But if both are true, then they'd need to be summed.

Otherwise (that is, if the row isn’t tagged as “Prebill,”), I need the [Total Amount Invoiced] to follow this formula (which works on its own, but maybe there’s a neater way to even do this piece!):

SUMIF([First Draft Invoicing]@row, "Invoiced", [FD Actual Invoiced]@row) + SUMIF([Final Invoicing]@row, "Invoiced", [Final Invoicing Amount]@row)

Thanks for your help!

Nick Korna

Hi @Lydia Bowman,

If I've understood you correctly:

=IF(AND(HAS([Add-ons]@row, "Prebill"), [Final Invoicing]@row = "Invoiced"), [Prebill Actual Invoiced]@row + [Final Invoicing Amount]@row, IF(HAS([Add-ons]@row, "Prebill"), [Prebill Actual Invoiced]@row + IF([Final Invoicing]@row = "Invoiced", [Final Invoicing Amount]@row, 0), SUMIF([First Draft Invoicing]@row, "Invoiced", [FD Actual Invoiced]@row) + SUMIF([Final Invoicing]@row, "Invoiced", [Final Invoicing Amount]@row)))

Sample output (probably with some scenarios that shouldn't happen around pre-bill invoicing):

Hope this helps, but if I've got something mixed up then just let us know!

Lydia Bowman

Hi @Nick Korna,

This solution worked perfectly — thank you SO MUCH! Our team is incredibly grateful. We appreciate it!