I have this table :


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 :


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 ?


enter image description here

1 Answer 1


Use query() like this:

  "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", 

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.