Sum/Multiply every value near the same name

Question

I'm trying to find a formula that can help me in a problem.
This formula should SUM (or work) with every cell that is near (always on the left) a cell that has the same name. Like below:

Consider every capital letter as a "recipe" and every lowercase letter as an "ingredient". To the left of every ingredient there's a number that indicates the amount of ingredient needed in that recipe.
At the left of the table there's the list of the ingredients.
Using COUNTIF i can know how many recipes have the same ingredients, but i'm searching something to sum (or multiply, it's the same once i understood the process) every left value of the same ingredient.
As you can see i can't use SUMIF (or SUM(FILTER()) ) since for example the element "b" can be found on the 3rd and 5th column and they're not on the same column.
I tried to use INDEX() and MATCH(), and could've used also LOOKUP(),VLOOKUP(),HLOOKUP(), but as you can see there are multiple instances of the same element, and the formula returns me only the same value (Also using ROW()-1 doesn't work for the multiple elements).

Now with this little table i can calculate the numbers easily (e.g. i know that element "a" is needed 2+3+2=7 times) but since the real table has 600+ elements between "recipes" and "ingredients" I wondered if there was a way to do this.

I'm working on OpenOffice but I have no problem on using Excel.

Thank you for your answers.

Seems like your concern is with different columns. But couldn't you just add up your SUMIF() results from the 3 different columns? — Marc
Yes, now that you told me I can build a general formula that pick the SUMIF() result of every column and work with that. I'm still wondering if there's a way to 1. search every "a" element, 2. subtract 1 at the row value, 3. pick the number and sum it with the next value (repeating 1. and 2. to search the values) — Iztooi
Why, in your description, does "a" count = 7, but in the example, the "a" count = 3? Using the description, your "b" count should be 7, but your example count shows "b" count as 3. Using the description, your "c" count should be 6, but in your example the "c" count is 2. Why are there these discrepancies? — tigeravatar
I may surely have been bad at explaining. The count column is the result of a COUNTIF() formula using the nearby criteria ("a", "b", "c"...) and the table range. I totally didn't mean to use that in the final formula, but in the end the column translation is "how many recipes use the listed ingredient?" . Sorry for the misunderstanding. — Iztooi

Tom Sharpe Tom Sharpe · Accepted Answer · 2016-09-30T18:32:56

Are you looking for something like this?:-

=SUM(IF(ISNUMBER($B$2:$F$7),$B$2:$F$7)*($C$2:$G$7=I7))

Must be entered as an array formula using CtrlShiftEnter

Sum/Multiply every value near the same name

1 Answers