Question involving various reference formulas

[TastySalad]

Hello,

I want cell B10 to contain a formula that does the following:

I need a formula that will sum the values in row 8, but I only want it to sum the values that correspond to a material that I specify from the table above. For example, I want cell B10 to total the amount of parts needed to make Granite. I want the whole table to be included in the formulas analysis, with the end result ultimately being the sum of G8, H8, I8, and J8 (because those parts are required to make Granite).

Ideally I want this all done in one formula, as this is a very sample of a huge table that has about 100 different raw materials that each draw from different ranges.

 

[skywriter]

Try this: You could replace the word with a reference to the cell that granite is in, or have a cell where you type granite, asphalt etc and reference that cell in your formula.

=SUM(INDEX(C3:J6,MATCH("Granite",A3:A6,0),0))

 

[TastySalad]

Try this: You could replace the word with a reference to the cell that granite is in, or have a cell where you type granite, asphalt etc and reference that cell in your formula.

=SUM(INDEX(C3:J6,MATCH("Granite",A3:A6,0),0))

This seems to be on the right track, but it's not quite there yet. This formula summed the values in row 4, I want it to sum only the values in row 8 that match up with whether or not Granite uses that part type.

I want it to add the values in row 8 based off of whether the "granite" row in the table has a value greater than 0. So, for this example, the correct sum I want is 45 (30+15+0+0).

 

[skywriter]

I want it to add the values in row 8 based off of whether the "granite" row in the table has a value greater than 0. So, for this example, the correct sum I want is 45 (30+15+0+0).

That makes more sense to me. Your original post didn't make sense since some of the totals below the granite numbers were zero, so I thought you meant you wanted what was in the granite row.

Try these, they array formulas, you must hit control shift enter after typing the formula into cell B10

=SUM(IF(INDEX(C3:J6,MATCH("Granite",A3:A6,0),0)>0,C8:J8))

or

=SUMPRODUCT(IF(INDEX(C3:J6,MATCH("Granite",A3:A6,0),0)>0,C8:J8))

 

[TastySalad]

ADVERTISEMENT

=SUMPRODUCT(IF(INDEX(C3:J6,MATCH("Granite",A3:A6,0),0)>0,C8:J8))

Both of your suggested formulas gave me an error, but I ended up taking out the IF function from this one and it got us a step closer. So, what I have now is:

=SUMPRODUCT(INDEX(C3:J6,MATCH("Granite",A3:A6,0),0),C8:J8)

This formula's result was 8.25. This is a lot closer to what I want, as it did a sumproduct of (0.15*30)+(0.25*15)+(0.1*0)+(0.1*0). However, I still only want it to add (30+15+0+0), and NOT to sumproduct it with the values in the table array.

 

[skywriter]

Both of your suggested formulas gave me an error, but I ended up taking out the IF function from this one and it got us a step closer. So, what I have now is:

=SUMPRODUCT(INDEX(C3:J6,MATCH("Granite",A3:A6,0),0),C8:J8)

This formula's result was 8.25. This is a lot closer to what I want, as it did a sumproduct of (0.15*30)+(0.25*15)+(0.1*0)+(0.1*0). However, I still only want it to add (30+15+0+0), and NOT to sumproduct it with the values in the table array.

They are array formulas you must confirm with control shift enter. I recreated your data and they both worked exactly as you wanted. It's not the formula.

 

[TastySalad]

Oh crap I'm sorry I forgot to do that. Yup, it definitely works - Thank you!!

 

[skywriter]

Oh crap I'm sorry I forgot to do that. Yup, it definitely works - Thank you!!

My pleasure. Good luck!