Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    Ok, I still haven't got my head around SUMPRODUCTS yet. But I reckon I can use one in this case.

    I have a worksheet with some hourly rates for different grades per year.

    2009 2010 2011
    A 50 60 70
    B 60 70 80
    C etc. etc.
    D

    I then have a different worksheet "Hours" with some rows with the following


    Hrs Grade
    10 A
    20 B
    30 C
    40 A
    14 C
    etc.

    I want to find the total costs for the Hrs column i.e. i want to take the hours of each row and multiple by the hourly rate associated with each grade (I have a VB function to find the hourly rate given the grade).

    In VBA, can the total cost for the hours in the Hrs column be calculated using a SUMPRODUCT? I do NOT want to add the hourly rates as a column to the "Hours" worksheet using a VLOOKUP or similar - i want to do that in VBA.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes, it can. You might as well simply modify your existing function to take the number of hours as an additional argument and then multiply that by the rate you are already retrieving.
    FYI, in terms of auditing and workbook clarity, I would personally add the rate column. You also don't need VBA to retrieve the rate based on what yuou posted - a simple INDEX and MATCH formula would do it.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    [quote name='rory' post='776413' date='22-May-2009 13:33']Yes, it can. You might as well simply modify your existing function to take the number of hours as an additional argument and then multiply that by the rate you are already retrieving.
    FYI, in terms of auditing and workbook clarity, I would personally add the rate column. You also don't need VBA to retrieve the rate based on what yuou posted - a simple INDEX and MATCH formula would do it.[/quote]


    Hi, thanks for the reply.. can you give me an example of both suggestions?

    I can create a function to calculate the cost (by getting the rate and multiplying by the hours) e.g. GetCost(rate,hours)

    Cost = GetCost(GetRate(grade), hours)

    but how do I incorporate that into a SUMPRODUCT do to it all in one go?

    I am also not familiar with the INDEX and MATCH functions so any pointers appreciated.

    cheers,
    Dom

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    This is how I would do it, using SUMPRODUCT, INDEX and MATCH.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    [quote name='rory' post='776419' date='22-May-2009 13:52']This is how I would do it, using SUMPRODUCT, INDEX and MATCH.[/quote]

    Hi, yes i understand that bit fully now. Thanks.

    I now realise that my example wasn't the best. I have updated the spreadsheet to be more realistic. The reason your suggestion won't work in its current form is that I have many columns containing hours, one column per year in fact. I need to total the costs for each year (column) and put them in the "Finance Report" worksheet. Therefore I can't add a column for the costs, since I would need a column for each year - and I can't do that because the "hours" sheet would double in size (and it's of no interest to the people who input the data).

    If you could suggest how you might get the costs for each year in to the "Finance Report" with the spreadsheet I returned to you, I would be extremely grateful!!

    cheer,s
    Dom
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    How's this?
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    [quote name='rory' post='776432' date='22-May-2009 14:57']How's this?[/quote]

    cool... can I do that in VBA? i.e. so the total doesn't appear in the hours sheet?

    (the argument about whether this is necessary is a different one )

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The total has to appear somewhere though - if you just want it on the summary sheet, then just use the formulas on the summary sheet instead.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    [quote name='rory' post='776437' date='22-May-2009 15:15']The total has to appear somewhere though - if you just want it on the summary sheet, then just use the formulas on the summary sheet instead.[/quote]


    ok.. one last question then.. if the columns in the hours sheet were months/years and not just years (e.g. Jun 09, Jul 09, Aug 09 etc), it would be more difficult to look up the right rate since you'd have to extract the year first.

    Can you build this into the formula?

    I have attached my real work in progress so I am not trying to recreate examples!

    In the real sheet, the "WBS" sheet contains all the hours per month. I need the totals to put in an "A15" sheet ("Financial Report" in my example) which is generated by pressing the "recycle" button on the WBS page. One A15 sheet is generated per year and i need to calculate the costs and cumulative costs for each year. The A15 sheet is generated by the "GeneratePSS_A15" function in the "PSS" VB module. It's quite straight forward.

    You can see I have tried to apply your formula to the "WBS" sheet along the top (because I can't predict how many rows there will be), but it didn't work - the months along the top will not always be the same so I can't hardcode which year's rates I am using to calculate the costs.

    cheers...
    Attached Files Attached Files

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    [quote name='dom_donald' post='776441' date='22-May-2009 15:32']ok.. one last question then.. if the columns in the hours sheet were months/years and not just years (e.g. Jun 09, Jul 09, Aug 09 etc), it would be more difficult to look up the right rate since you'd have to extract the year first.

    Can you build this into the formula?

    I have attached my real work in progress so I am not trying to recreate examples!

    In the real sheet, the "WBS" sheet contains all the hours per month. I need the totals to put in an "A15" sheet ("Financial Report" in my example) which is generated by pressing the "recycle" button on the WBS page. One A15 sheet is generated per year and i need to calculate the costs and cumulative costs for each year. The A15 sheet is generated by the "GeneratePSS_A15" function in the "PSS" VB module. It's quite straight forward.

    You can see I have tried to apply your formula to the "WBS" sheet along the top (because I can't predict how many rows there will be), but it didn't work - the months along the top will not always be the same so I can't hardcode which year's rates I am using to calculate the costs.

    cheers...[/quote]

    This version should do it.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    [quote name='rory' post='776446' date='22-May-2009 15:51']This version should do it.[/quote]


    thanks for your help.. that does the trick.. i understand the formula but i guess it takes experience to be able to come up with those monsters by yourself

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    [quote name='dom_donald' post='776453' date='22-May-2009 16:26']thanks for your help.. that does the trick.. i understand the formula but i guess it takes experience to be able to come up with those monsters by yourself [/quote]

    Beer sometimes helps, too...
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •