Google Sheet ArrayFormula in Excel 365

user_9759946 0
Viewed 61

Hi I am using google sheet for my daily calculation and I have used ArrayFormula which is working fine. Now, I want to shift my data from Google Sheet to Office 365 Excel. I tried the same formula there but it's not working.

Users Days Worked
Bas 10
Walter 10
Bas 10
Walter 15
James 5
James 10

Formula Used

={unique(A18:A25),ArrayFormula(sumif(A18:A25,unique(A18:A25),B18:B25))}

End Result

Users Days Worked
Bas 20
Walter 25
James 15

In Google Sheet is this formula is filtering unique values and gives the sum of related values. I tried the same method but it's not working. Please suggest any post or video where can I find the same methodology if possible. Thanks in Advance

office365 google-sheets excel-formula excel
1 Answers

You can't use the curly bracket notation in the same way in Excel but there is a workaround using Choose to combine two arrays:

=CHOOSE({1,2},UNIQUE(A18:A25),SUMIF(A18:A25,UNIQUE(A18:A25),B18:B25))

However, in both Google sheets and Excel you will notice an extra row corresponding to the blank cells in the range A18:A25, so you will need to use filter as well:

=CHOOSE({1,2},UNIQUE(FILTER(A18:A25,A18:A25<>"")),SUMIF(A18:A25,UNIQUE(FILTER(A18:A25,A18:A25<>"")),B18:B25))

enter image description here

user_38949170
Related