Creating a forumla to pick values in a row, then multiplying by another figure

**wench02** · 11-06-2012, 09:46 AM

Bit of a long one, and I really need help with this. I am sure there will be a way of doing it, but it's beyond me.

I have a spreadsheet that I have numerous tabs in, for daily record on hours worked for both labourers, and machines, which is then is totalled at the bottom to give me a weekly cost. The problem comes when I need to allocate the daily costs to different cost codes.

What I would like to do is to create a formula that picks up the hours in each row, calculates it with the appropriate cost, then adds in all together, so I get a “lump sum” daily figure cost for that day.

I can provide an example sheet for anyone to see that may be able to help me, as trying to explain is rather hard!

**abousetta** · 11-06-2012, 09:51 AM

An example would be very helpful.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

**TheExcelphile** · 11-06-2012, 09:52 AM

We can certainly help you, but we do need the example sheet!

**wench02** · 11-06-2012, 10:52 AM

example.xls

Hopefully this will work...

**wench02** · 11-07-2012, 06:02 AM

Anyone any ideas?

**TheExcelphile** · 11-07-2012, 10:34 AM

I'm sorry, Wench, but your sample sheet is a little confusing. How do you assign the cost codes? Could you perhaps give us a result that you want, along with how you arrived at the result?

**wench02** · 11-07-2012, 10:38 AM

I assign the cost codes manually. I have typed "yes" into the cost code cell that I want the formula to appear in.

In the "real" version of the spreadsheet there are many more labourers, equipment and cost codes.

**TheExcelphile** · 11-07-2012, 10:47 AM

I think a sumproduct formula should work for you.
=SUMPRODUCT($B6:$H6,$B$11:$H$11)
This formula, when inserted in I6 in your sample, will take all the hours for labour and equipment in that row (in this case, row 6), multiply with the corresponding rates in row 11, and give you the total cost.
I hope this is what you were looking for.

Don't forget to click the small star next to this post if you feel I helped.

Creating a forumla to pick values in a row, then multiplying by another figure

LinkBack

Thread Tools

Rate This Thread

Display

Creating a forumla to pick values in a row, then multiplying by another figure

Re: Creating a forumla to pick values in a row, then multiplying by another figure

Re: Creating a forumla to pick values in a row, then multiplying by another figure

Re: Creating a forumla to pick values in a row, then multiplying by another figure

Re: Creating a forumla to pick values in a row, then multiplying by another figure

Re: Creating a forumla to pick values in a row, then multiplying by another figure

Re: Creating a forumla to pick values in a row, then multiplying by another figure

Re: Creating a forumla to pick values in a row, then multiplying by another figure

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions