INDEX/MATCH/SUM FORMULAS

JayF

Ok so I'm not very familiar with INDEX/MATCH formulas but I'll explain what the goal is. So I have the first set listing all the materials. Now on the second picture I have what was delivered. There will be multiple orders under Order History that will list the same item. I want all those items summed up and placed in the Total Received column where it lists Current Materials. I've tried using =INDEX([Total Received]:[Total Received], MATCH("JUS28", [Item]:[Item], 71, 73) as an example but it's saying incorrect, and on top of that I'm hoping I don't need to stop at 73, I'm looking for it to be 71 until infinity because I'm going to continue adding orders until that Project is finished. The last Parent row that says deliveries is information entered through a form and the only info that goes there is Delivery Date and Vendor so nothing will be in the other columns below that row.

Side note I realize my materials have ", I don't mind removing those if that's going to be an issue in the formula. Hoping someone can help!

Paul Newcome

Try a SUMIFS instead.

JayF

@Paul Newcome Thanks Paul, I didn't think of that, so I tried that with this formula =SUMIF([Item]71:[Item]74, "JUS28", [Total Received]71:[Units Sold]74) , but it's saying unparseable. Also is there a way to not have 74 and just make the rows infinite as I'll be continuing to add to them?

Paul Newcome

Make sure the column names are an exact match to what are being used in the sheet. Instead of typing the column names, you can click on any cell in the column the delete the row number.

To reference an entire column and include new rows as they are added, you would remove the specific row numbers so that it is just

[Column Name]:[Column Name]