IF/OR Formula Issue with writing

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?

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!

All comments

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?

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

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

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!

If I've understood you correctly:

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!