Google-Sheets : ARRAYFORMULA() for multiple columns

Question

I have this table :

https://docs.google.com/spreadsheets/d/100ij18btv5VTmnwwdaFJ8U3PEzkS8Z-duEn2PKgA6sc/edit?usp=sharing

There are 3 Sheets inside it.

The idea is to get how many ingredients used each day. For example as in Sheet1 in day 1 :

cake1 uses 2 chocolate
cake2 uses 0 chocolate
cake3 uses 0 chocolate
cake4 uses 3 chocolate

So in day1, there are 5 chocolate used.

I use this formula in C3 :

=sumif($B$10:$B$28,$B3,C$10:C$28)

Then i copy/paste to range C3:J7 and it works as i expected. Now, i want to make it as an arrayformula . So i modify it in Sheet2 :

=arrayformula( sumif($B$10:$B$28,$B3:$B7,C$10:C$28) )

it works for single column (in C3:C7 of the Sheet2 in link i share). I can then copy paste for other columns but i want to try to make arrayformula to works for the whole range C3:J7.

In Sheet3 , it populate the formula across the range C3:J7, but the value is wrong.

Can someone help if it's possible to make my arrayformula to apply to multiple columns ?

Thanks

Answer 1

Use query() like this:

=query( 
  B10:J, 
  "select B, sum(C), sum(D), sum(E), sum(F), sum(G), sum(H), sum(I), sum(J) 
   where B is not null 
   group by B", 
  0 
)

The ingredients will be listed in alphabetic order. You can change that with an order by clause. To change or remove the headings, use a label clause. See the query language help page.