SUMIF? - multiplying two commissioing rates

[3smees23]

Hi,

My spreadsheet is nearly complete thanks to you guys.

In C52 'Take-Off' .......

I'm trying to multiply C51 by C29. However if there is a figure in C63 then I need the formula to allow for the increased cost (C61).

I have highlighted the above cells in yellow.

Thanks in advance

Sam

[jeffreybrown]

Are you looking for...

=IF(ISNUMBER(C63),(C59+C61)*C51,C51*C59)

Maybe I'm not seeing your requirement right if C63 contains a number

[3smees23]

Hiya,

That multiplied the commissioing cost (C52) by 3. Which is not what I need unfortunately.

Normal working hour commissioning would be £8640, but because I have 3 in C63 I need C52 to be £9450.

This is because im uplifting 3 days from £270 to £540 (540 is the out of hours cost in C61).

Hope this makes sense

Cheers

Sam

[jeffreybrown]

Hi Sam,

Since I do not know if C61 will always be 540, this is taking C61-C59 multiplied by the number of days in C63 and adding it to the original formula but only is C63 is a number. If C63 is not a number then your original formula takes over.

Is this what you are seeking?

=IF(ISNUMBER(C63),C51*C59+((C61-C59)*C63),C51*C59)

[3smees23]

Hi Jeff,

This is exactly what I'm after. The commisioing cost (C59&C60) will never change but the sell prices (C61&C62) and amount of OOH days (C63) will.

But it works like a dream.

Many thanks

Sam

[jeffreybrown]

You're welcome Sam…glad it worked for you and thanks for the feedback...

[Cutter]

Just a thought:

since C63 appears to be a typed value as opposed to a formula which either returns a number or "" then you only need

=C51*C59+((C61-C59)*C63)

so, when C63 is blank it equates to 0 which adds 0 to C51*C59

[jeffreybrown]

@Cutter,

Thanks for taking the time to pass along some insight.

I had given thought to the C51*C59 being duplicated, but didn't have an answer; however, this makes sense.

[Cutter]

You're welcome, Jeff. Thanks for the 'star tap".

[Cutter]

An added thought:

=C51*C59+((C61-C59)*SUM(C63)) would take care of a possible unwanted text value in C63