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!

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

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

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

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

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

Normally Enter

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?

Hi

exactly where is a problem?

rylo

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?

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

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

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!

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