SUMIFS and ARRAYFORMULA within Google Sheets-CodePudding

I have a google sheet with multiple tabs, one of the tabs is for holding each observation of data while another needs to combine data based on certain criteria. I am trying to use a SUMIFS within and ARRAYFORMULA to get the correct information and it will only pull "0" no matter what I try.

I have set up a test google sheet with some dummy information to show an example of what I need to do in a more complex situation.

CodePudding user response：

you cannot use SUMIFS() in Arrayformula(), along with many other functions, though there is no formal documented list.

In your case you can use a SUMIF() instead by &'ing the condtions together.

I've demoed the concept on a new tab called MK_Help in cell D2:

=ARRAYFORMULA(IF(ROW(A2:A) = ROW(A2), "# TOTAL TYPE", IF(A2:A = "", , SUMIF(data!A:A&data!B:B&data!C:C,A2:A&B2:B&C2:C,data!E:E))))

Note that I made a couple of other small changes to your formula.

Namely, that you should always use a true "empty" instead of double quotes in your IF() mask up front tor return empty when there's no value in A. Double quotes("") is actually not quite empty for many other things in Google sheets.

Also I modified your header conndition from ROW(A2:A)=2 to ROW(A2:A) = ROW(A2). I find that this is a more flexible condition for the header as it allows you to potentially insert/delete rows above the header without breaking things.