Use SUMIFS in an ArrayFormula Google Sheets

Question

I've already seen a lot of topics about my problem, but I can't resolve it. Actually, I don't understand what is wrong in my formula ?

=ArrayFormula(
    IF(
        ISBLANK(A6:A);;SUMIFS(
            Sheet1!J:J;
            Sheet1!K:K; ">="&A6:A;
            Sheet1!K:K;"<="& EOMONTH(A6:A;0)
        )
    )
)

What I'm trying to do : Each line is a month. I want to SUM all sales made between the first day on the month, and the last one.

If I don't use ArrayFormula, it works. I dont understand how to write this formula to work with ArrayFormula.

Thank you very much for your help !

Thanks @Datanovice . Here's the sample sheet (with few data) : docs.google.com/spreadsheets/d/… — Alexis
hey buddy, just had a look at your Formula and it works for me? are you trying to understand how arrays work? you could also work out this answer by using a google query — Umar.H
Thanks @Datanovice for your answer. Yes, I'm trying to transform XXX formulas into one ArrayFormula. I don't succeed to do it because my formula must include ranges... and I don't how how tu use ranges with ArrayFormula. — Alexis

Umar.H Umar.H · Accepted Answer · 2018-09-20T18:17:32

Just adding this as an answer for you,

Instead of using an array formula, I believe a query would serve you better.

as google date-time syntax is tricky we first have to convert the date value into a text and parse it as "yyyy-mm-dd" we then wrap the EOMONTH to get the last day of the month.

so we use the following formula to get the sum of the months sales:

    =query(A:B;"Select sum(B) 

where A <= date '"&text(eomonth(E14;0);"yyyy-mm-dd")&"' 

AND A >= date '"&text(E14;"yyyy-mm-dd")&"' label sum(B) ''  ";0)

hopefully, this is what you were after, apologies for the delay was on the train!

https://docs.google.com/spreadsheets/d/1ygppZZCd4b_Y_HufLwLdcynHAsa3pn6z5YXb3Poc3vk/edit?usp=sharing

Use SUMIFS in an ArrayFormula Google Sheets

1 Answers