# Question involving various reference formulas

##### New Member
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.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.

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

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.

Code:
``=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).

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

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

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

Last edited:
Code:
``=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:

Code:
``=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.

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:

Code:
``=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.

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

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

My pleasure. Good luck!

Replies
1
Views
457
Replies
1
Views
598
Replies
5
Views
308
Replies
14
Views
5K
Replies
4
Views
370

1,196,481
Messages
6,015,451
Members
441,896
Latest member
clomah

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back