Summing array in Excel

Question

I have four tables. The first one shows amount of ingredient each menu uses.

Menu_Ingredient

  menu  | ingredient | amount
--------+------------+--------
  Foo   |   A        |  0.1
  Foo   |   B        |  0.2
  Bar   |   A        |  0.3
  Baz   |   C        |  0.4

Then I have the transaction table which shows date and the menu sold.

Menu_Sold

  date  | menu sold
--------+------------
   x    |   Foo
   x    |   Foo
   x    |   Bar
   x    |   Baz

I leave the date blank, assuming they are all the same for now. The final table that I want is showing how much ingredient was used each month. As shown in column A,B,C. To do this I created another table in column F,G,H.

     A         B          C           F       G      H
   month | ingredient | amount      month | menu | amount
  -------+------------+--------   --------+------+--------
3    x   |     A      |   ?           x   |  Foo |   2
4    x   |     B      |   ?           x   |  Bar |   1
5    x   |     C      |   ?           x   |  Baz |   1

Here is the formula I used to count the amount in H column.

=COUNTIF(Menu_Sold!B:B,Monthly_Ingredient_Used!G4)

Here is what I did to find amount of ingredient A.

1. Get array of menus that contains ingredient in A3 =IF($B$3=Menu_Ingredient!B:B,Menu_Ingredient!A:A,"")
2. Get amount sold in that month (here assumming all entries are in the same month, thus left blank)
   =IFNA(INDEX(H:H,MATCH(IF($B$3=Menu_Ingredient!B:B,Menu_Ingredient!A:A,""),G:G,0)),0)
3. Multiply by amount of ingredient used
   =IFNA(INDEX(H:H,MATCH(IF($B$3=Menu_Ingredient!B:B,Menu_Ingredient!A:A,""),G:G,0)),0)*Menu_Ingredient!C:C
4. SUM all amount
   =SUM(IFNA(INDEX(H:H,MATCH(IF($B$3=Menu_Ingredient!B:B,Menu_Ingredient!A:A,""),G:G,0)),0)*Menu_Ingredient!C:C)

Here is an image of the result I got

Questions:

1. The first three steps give the right result. When I expand an array down the column, I can see correct numbers. However in the last step, it doesn't sum the whole array and give the first element as a result.
2. I also tried using VLOOKUP instead for step 2, but somehow it gives wrong lookup value and I don't understand why.

Here is the file if you want to try it out. https://1drv.ms/x/s!AgK3RJYPrJR8iGsfvXV0rrhtLiZa

Answer 1

Please check if this will work for you.

It looks like the table that you have created will give you outcome for A. If you want for B, you will have to create another table. From what I best understand your question, I suggest a slightly different approach which requires you to create a single Helper Table with number of columns = number of Menu items.

Here's an example. To keep it simple and accommodate in a single screenshot, all tables are in a single sheet.

Table 3, 4 & 5 are as what you have in your sheet (Table numbers may vary though)

See the Helper Table. Here each column is per Menu Item. So Foo, Bar & Baz in this case. This is side by side with Table 1, the final outcome table.

I have added one more ingredient say D in this example.

Now in D3 put the following formula and drag it across the rows and columns of this table.

=SUMPRODUCT(($P$3:$P$7=$B3)*($O$3:$O$7=D$2),$Q$3:$Q$7)

This gives the Quantity of A used in Foo, Bar & Baz in Row 3. Similarly on the next row 4 it gives Quantity of B used in the same ingredients.

Now to get Amounts used all you need to do is use SUMPRODUCT

Formula in C3 is

=SUMPRODUCT(D3:F3,TRANSPOSE($J$3:$J$5))

Since J3:J6 are transposed to match the Array in SUMPRODUCT the entire formula needs to be an Array Formula. Press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The Formula shall now be enclosed in Curly Braces to indicate that it's an Array Formula. Drag it down along the column and for each Ingredient, you shall get the amounts consumed.

Note that here Month is not coming into picture as how you have treated it for now in your example.. If Month is to be added, formula might get more complex.