I use Arrayformula() to make my reports dynamic and easier to edit. For example, if I have a Column A with a list o number o blue balls in a set and a Column B with a list red balls in a set, on the cell C1 I can write =ArrayFormula(add(A1:A,B1:B)) and in the Column C will have the total of balls in each set. It would be exactly the same as writing =A1+B1 in cell C1 and dragging the formula down to the last row. Arrayformula() has some benefits, because it will work if some adds or removes rows from the sheet and also it makes the reports way more organized and easier to edit.
Since I´ve discovered arrayformula(), my life has changed, because of the fact that googleSheets expands the formula to other cells. It does not work every time, but the idea of expanding to other cells seems to be possible some way or another, here is a good example of a problem that was not resolved by arrayformula(), but has the same idea.
Keeping this idea in mind, I want to do a sumif using arrayformula to expand the function to all the cells in a range. The sumif part is really simple. Imagine I have on column A names of children, on column B I want to write the sum of all the balls each child has. This data I gather from a second table. In this second table I have in column D the name of the child, in column E the color of the ball and in Column F the number of balls of that color.
My sumif for the first child in row 1 would look like =sumif($D:$D,A1,$F:$F). If I drag this formula all the way down column B, I would get the desired result. However, I want to do this with a formula that expands. I´ve tried arrayformula(sumif($D:$D,A1:A,$F:$F)) and it does expand the formula, but apparently it does not work.
Here is the spreeSheet where this formula is not working. Some cells are with the wrong value.
Does some one has an alternative?
