0

I have a spreadsheet with three columns: one for dates, one for daily income and one for daily change rates.

enter image description here

I want to have a sum of all the daily income grouped by month as seen above. For that, on cell E2 I have the following formula:

=ArrayFormula({unique(text(A2:A,"MMMYY")),
 sumif(text(A2:A,"MMMYY"),unique(text(A2:A,"MMMYY")),B2:B)})

It works well. Now, I would like to calculate the monthly average from the daily average (grouped by month). This is what I tried:

=ArrayFormula({unique(text(A2:A,""MMMYY"")),
 sumif(text(A2:A,""MMMYY""),
 unique(text(A2:A,""MMMYY"")),B2:B), AVERAGEif(text(A2:A,""MMMYY""),
 unique(text(A2:A,""MMMYY"")),C2:C)})

It doesn't work, it gives out the following:

ErrorFunction ARRAY_ROW parameter 3 has mismatched row size. Expected: 4. Actual: 1.

Edit: In the end, I found a way to make it work using query:

=query({ArrayFormula(EOMONTH(A2:A82,0
)),B2:B82,C2:C82},"Select Col1, Sum(Col2),avg(Col2) group by Col1 format Col1'MMM-YY'")

Any ideas how to solve this without query?

Thanks!

1 Answer 1

1

Please use the following

=ArrayFormula({unique(text(A2:A,"MMMYY")),
 sumif(text(A2:A,"MMMYY"),unique(text(A2:A,"MMMYY")),B2:B), 
 sumif(text(A2:A,"MMMYY"),unique(text(A2:A,"MMMYY")),B2:B)/ 
 COUNTIF(text(A2:A,"MMMYY"),unique(text(A2:A,"MMMYY")))})

What we actually do is sum/count=average

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.