0

here's a tricky one for me, hopefully not for you.

I have a data table in one sheet which lists product numbers in the column, and materials used to make said product in the top row, with corresponding quantities where they meet.

In a second sheet, I have a daily list that consists what products were produced with a corresponding quantity produced in another column.

In my third sheet, I'd like to be able to see the totals of what materials were used that day depending on what products were produced

Ex. Daily sheet shows out of the list that 80 units of product 1 was produced. It takes 1 unit of material x to produce the product. Therefore Id like a formula to show me I used 80 units of material x, showing up under that column header.

Any help is appreciated, I feel like Im close but making it way too complicated and cant get it to work...

Thanks!

migrated from stackoverflow.com Mar 8 '13 at 10:27

This question came from our site for professional and enthusiast programmers.

  • show us some of what you've tried. Will a sumif do the trick here? – Kevin Mar 7 '13 at 3:54
  • OK, it's easy if your sheet two has all the same product numbers, and if they weren't produced has a zero (or is blank) for a quantity. Is that the case? – Lance Roberts Mar 7 '13 at 4:59
  • sumif alone didnt work, a response below using sumif and sumproduct did the trick, guess two dimensional lookup wasnt the right path. – user2142544 Mar 7 '13 at 20:54
1

For the solution I assume the following, slightly simplified setup:

enter image description here

To get to this result, enter image description here

do the following:

  • Add an new column next to your table with the quantities (here it is column E). In here you sum the amount of products sold. I used this formula:
    =SUMIFS(I:I,H:H,A4)
    . Depending if you want the sales for a specific date, you can of course adjust this formula, as at the moment it sum the products from all dates.
  • Add a table with all the materials
  • Calculate the the sum with the following formula
    =SUMPRODUCT(($B$4:$D$6*$E$4:$E$6)*($B$3:$D$3=$K4))
  • This works great! Different idea from what I was going for but awesome thank you. – user2142544 Mar 7 '13 at 20:52
1

Heres a strategy that uses a combination of Excel Tables and a Pivot Table to report the results

(Developed in Excel 2010)

  • Configure your Product/material usage data as a Table: I've called it tblQuantities
  • Configure your Daily Production data as a Table: I've called it tblProduction
  • Extend tblProduction with formulas to get the material consumption per Product per Material ID
  • Set tblProduction to auto extend the formulas as you add new rows
  • Add a Pivot Table to get the summed daily consumtion data

tblQuantities

enter image description here

tblProduction

enter image description here

Formula for tblProduction Material columns

=INDEX(tblQuantities,MATCH([@Product],tblQuantities[ProductID],0),
 MATCH(INDEX(tblProduction[#Headers],COLUMN()),tblQuantities[#Headers],0))
 *[@Quant]

Pivot Setup

enter image description here

Pivot Result

enter image description here

  • This seems more complex but more detailed, will try this out and let you know, thanks for the detailed answer. – user2142544 Mar 7 '13 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy