SUMPRODUCT - Index and Match function two possible outcomes

[Chambew]

Hello all,

I am currently using the following formula to bring me back a gross margin from my source data - Column K

=SUMPRODUCT(INDEX(Starters!$X$1:$AA$8,MATCH(F6,Starters!C:C,0),)*{-1,1,-1,1})*5

Which works fine except in the latest iteration I am using the source data some other values are present which don't tally correctly.

The '*5' is to take the Gross Margin of workers daily rates and times it by 5 for the week (Example Example), however, I now have values present in 'Starters!$X$1:$AA$8' which are hourly rates, so I would need it be the value times 8, then times 5 but without it multiplying a daily rate by 8 and then 5 (Test Test).

I have included a crude test file to illustrate.

[Chambew]

Can anyone assist?

[Chambew]

Hi all, I was wondering if inserting a new column for hours to be entered would be a suitable fix?

But I am not sure how to amend - =SUMPRODUCT(INDEX(Starters!$X$1:$AA$8,MATCH(F6,Starters!C:C,0),)*{-1,1,-1,1})*5 - to include the option of multiplying the value this formula produces by a number of hours from another cell.

Is this possible?

[JeteMc]

If I understand correctly the values in columns X:Y are daily and in columns Z:AA are hourly. Since the two cases are exclusive try the following in K6 and down on the Cont 1 sheet:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Chambew]

Thank you, that has worked!

[6StringJazzer]

Chambew, it looks like you edited your title to show it's solved. The correct way to mark a thread SOLVED is to:

Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

I did it for you this time since you are a new-ish user.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved' (with Jeff's help). I hope that you have a blessed day.