Writing a reference formula with multiple IF/AND situations

Lydia Bowman

Hi,

I'm looking to write a formula using references to a rate sheet (screenshot attached with sensitive information redacted). We have several types of rates here, and also ones that are per word, which would be inputted into the main project sheet.

We're trying to write a formula right now for the "Role" selection. So, if the Client Name = X, Role = X, we need to pull that into the main project tracker and multiply it by a Word Count column or an Hourly Time column or be a flat fee, depending on what it is.

So far, I've only gotten part of the way there:

=IF([Client Name]@row = "John Smith", SUMIFS({Role}, {Project Type}, HAS([Status]@row, @cell)) <-- gives no error but gives no result

=IF(ISBLANK(Status@row), "", SUMIF({Role}, Status@row, {Rate})) * [Word Count]@row <--- gives a result, but not a correct one

Not quite sure how to reflect all the conditions here. I have references created for all columns minus the Rate Unit and Rate Type, which may need to be created? Any thoughts anyone has for this would be super appreciated! Thanks so much.

jmyzk_cloudsmart_jp

Suppose you have a rate sheet like this;

Smartsheet

And suppose your Project Tracker sheet is like this;

Smartsheet

As you can see, I added those helper columns to calculate the cost.

[Rate] =IF(ISTEXT(Role@row), INDEX(COLLECT({Rate_Sheet_Sample : Rate}, {Rate_Sheet_Sample : Client Name}, [Client Name]@row, {Rate_Sheet_Sample : Role}, Role@row), 1))
[Rate Unit] =JOIN(COLLECT({Rate_Sheet_Sample : Rate Unit}, {Rate_Sheet_Sample : Client Name}, [Client Name]@row, {Rate_Sheet_Sample : Role}, Role@row))
[Units] =IF([Rate Unit]@row = "Per Word", [Word Count]@row, IF([Rate Unit]@row = "Per Hour", [Hourly Time]@row, IF([Rate Unit]@row = "Per Unit", Unit@row)))
[Calculated Cost] =IF(ISNUMBER([Flat Fee]@row), [Flat Fee]@row, Rate@row * Units@row)

The formulas are designed to pull the correct rate, rate unit, and calculate the cost for a project based on the client name, role, and applicable word count, hourly time, or unit-based pricing. It achieves this by:

1. Retrieving the rate from a rate sheet based on the client and role.
2. Determining the rate unit (Per Word, Per Hour, or Per Unit).
3. Calculating the number of units based on the type of rate (word count, hours, or other unit-based calculations).
4. Computing the final cost by multiplying the rate by the determined units, unless a flat fee is specified.

jmyzk_cloudsmart_jp

Suppose you have a rate sheet like this;

Smartsheet

And suppose your Project Tracker sheet is like this;

Smartsheet

As you can see, I added those helper columns to calculate the cost.

[Rate] =IF(ISTEXT(Role@row), INDEX(COLLECT({Rate_Sheet_Sample : Rate}, {Rate_Sheet_Sample : Client Name}, [Client Name]@row, {Rate_Sheet_Sample : Role}, Role@row), 1))
[Rate Unit] =JOIN(COLLECT({Rate_Sheet_Sample : Rate Unit}, {Rate_Sheet_Sample : Client Name}, [Client Name]@row, {Rate_Sheet_Sample : Role}, Role@row))
[Units] =IF([Rate Unit]@row = "Per Word", [Word Count]@row, IF([Rate Unit]@row = "Per Hour", [Hourly Time]@row, IF([Rate Unit]@row = "Per Unit", Unit@row)))
[Calculated Cost] =IF(ISNUMBER([Flat Fee]@row), [Flat Fee]@row, Rate@row * Units@row)

The formulas are designed to pull the correct rate, rate unit, and calculate the cost for a project based on the client name, role, and applicable word count, hourly time, or unit-based pricing. It achieves this by:

1. Retrieving the rate from a rate sheet based on the client and role.
2. Determining the rate unit (Per Word, Per Hour, or Per Unit).
3. Calculating the number of units based on the type of rate (word count, hours, or other unit-based calculations).
4. Computing the final cost by multiplying the rate by the determined units, unless a flat fee is specified.