+ Reply to Thread
Results 1 to 18 of 18

Calculating total cost from range of cell tally's by cost of item.

  1. #1
    Registered User
    Join Date
    10-29-2012
    Location
    Pigeon Forge, TN
    MS-Off Ver
    Excel 2007
    Posts
    23

    Calculating total cost from range of cell tally's by cost of item.

    I have a workbook of supply control and pricing. The goal is to have B5 to AL5 as tally spots. Then have AM5 as the spot for total cost of supplies in accordance to the amount of tallys written in B5 to AL5. The trick is all of the cost data is broken down on another sheet.

    Much help needed! Have attached the xls file.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculating total cost from range of cell tally's by cost of item.

    Hi

    If you set things up so that the headings in Item Cost Data exactly (there are some differences, and for the Kitchen Supplies sheet, Medium Pizza Pan is missing) then you could do something like

    Kitchen Suppies!AM5: =SUM((B5:AL5)*(TRANSPOSE('Item Cost Data'!$B$3:$B$39))) (note this formula is array entered CTRL, SHIFT, Enter)

    HTH

    rylo

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Calculating total cost from range of cell tally's by cost of item.

    Another way although I went with the other tab...

    B3 on the Item Cost Data tab

    =IFERROR(SUM(INDEX('Appliances & Other'!$B$5:$AL$59,,MATCH(A3,'Appliances & Other'!$B$3:$AL$3,0))),0)

    On second thought maybe I read it backwards.
    HTH
    Regards, Jeff

  4. #4
    Registered User
    Join Date
    10-29-2012
    Location
    Pigeon Forge, TN
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Calculating total cost from range of cell tally's by cost of item.

    Thanks for this feedback... I corrected everything to match exact but I'm getting an error. Have attached new xls.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculating total cost from range of cell tally's by cost of item.

    Hi

    I put the formula I gave you in #2 into the advised cell, put a 1 in AL5 and it brought back a 20. Then I realised that you still don't have a cost for Medium Pizza Pan. So I inserted a blank row, corrected the formula to cover the correct range, and it brought back 15.

    So update your file for the missing cost item, and put in the formula and see how it goes. If you still have a problem upload the file with your formula attempt.

    rylo

  6. #6
    Registered User
    Join Date
    10-29-2012
    Location
    Pigeon Forge, TN
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Calculating total cost from range of cell tally's by cost of item.

    Okay, so I created the new line for Med. Pizza Pan... Inserted the formula and I still get a #Value error.... thanks for the input. Attached new xls with formula added.
    Attached Files Attached Files
    Last edited by jeffreybrown; 10-29-2012 at 09:37 PM. Reason: Forum Rule #12. Don't quote whole posts unless necessary-- it's just clutter

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculating total cost from range of cell tally's by cost of item.

    Hi

    You haven't array entered the formula. Select Kitchen supplies!AM5, press F2, hold down the shift and control keys, then press the enter key. You should find that the formula is then enclosed with {}.

    When you get to that stage, put a 1 in AL5, and it should return 15.


    rylo

  8. #8
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Calculating total cost from range of cell tally's by cost of item.

    =SUMPRODUCT(('Item Cost Data'!A3:A74=B3:AL3)*'Item Cost Data'!B3:B74)

    Normally Enter

  9. #9
    Registered User
    Join Date
    10-29-2012
    Location
    Pigeon Forge, TN
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Calculating total cost from range of cell tally's by cost of item.

    Sweet! I got it. The cost calculation is working but I'm getting little formula error indicators. Is there a way to eliminate them by adding something to the formula?
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculating total cost from range of cell tally's by cost of item.

    Hi

    exactly where is a problem?

    rylo

  11. #11
    Registered User
    Join Date
    10-29-2012
    Location
    Pigeon Forge, TN
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Calculating total cost from range of cell tally's by cost of item.

    I guess excel is flagging this formula and cells as having errors. Gives the little green triangle to the left of each cell. Any way to fix that?

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Calculating total cost from range of cell tally's by cost of item.

    Highlight range of cells >> square box will appear >> click box and ignore error

    You can also turn this off through Excel options >> formulas >> error checking

  13. #13
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Calculating total cost from range of cell tally's by cost of item.

    Hi

    The error for the input cells relates to the cell format being text. Change the input range back to general and reenter the numbers.

    rylo

  14. #14
    Registered User
    Join Date
    10-29-2012
    Location
    Pigeon Forge, TN
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Calculating total cost from range of cell tally's by cost of item.

    Sweet! Thank you for all the help. I'm good to go!

  15. #15
    Registered User
    Join Date
    10-29-2012
    Location
    Pigeon Forge, TN
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Calculating total cost from range of cell tally's by cost of item.

    Hello again!

    I have added a sheet to my workbook here that calculates totals from all pages.... having an issue with the formula. Should be simple but I'm getting an error. Could anyone take a look at the attachment, please?

    Thanks!
    Attached Files Attached Files

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Calculating total cost from range of cell tally's by cost of item.

    What exactly are you having problems with? Some direction please.

  17. #17
    Registered User
    Join Date
    10-29-2012
    Location
    Pigeon Forge, TN
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Calculating total cost from range of cell tally's by cost of item.

    I would like the Cabin Billing Totals Page to display a Grand Total at the bottom.... basically the sum of B6:B44.

  18. #18
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Calculating total cost from range of cell tally's by cost of item.

    How about

    =SUM(B6:B44,B46:B60)

    or

    =SUMIF($A$6:$A$60,"<>",$B$6:$B$60)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1