1

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.

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

1 Comment

That won't work because it is pulling all the rest of the data too. In my more complex sheet (sensitive information), it will have pulled in the other information based on some other queries so the formula needs to reference those and pull just the sum

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.