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
CodePudding user response：
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))
