CODEWITHSUNDEEP
google-sheetsarray-formulassumifs
data_life
data_life

Reputation: 399

SUMIFS and ARRAYFORMULA within Google Sheets

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.

https://docs.google.com/spreadsheets/d/1JLyEuVijQ8MvfOKrtbRD_YKmRDnTCxf7qCSw9Ggty_Y/edit#gid=1250575550

In this example, the data tab is the individual observations and the sums tab is where I'm trying to pull combinations. I need column D to sum the totals in column E on the data tab if the Month and Year and Type all match what is on the sums sheet. In this example, cell D3 on the sums tab should equal 11.

Upvotes: 1

Views: 2878

Answers (2)

MattKing
MattKing

Reputation: 7773

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.

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 36750

It seems QUERY() may be good choice. Try-

=QUERY(data!A2:E,"select A,B,C, sum(E) where A is not null group by A,B,C",1)

If you need specific month then you can add criteria to where clause like-

=QUERY(data!A2:E,"select A,B,C, sum(E) where A =5 group by A,B,C",1)

enter image description here

Upvotes: 0

Related Questions