Groupby in excel using only formulas

Question

I want to find sum of amount per C_ID where Payment Status = "PAID" by using functions only , no pivot tables.

I have tried using combo of unique() & sumifs(), but it is not working properly. The address of "PAID" keeps on changing to R6 , R7 , for each row.

Is there a better way to do this? I am using MS 365 online version.

Mark S. · Accepted Answer · 2024-04-24 21:03:45Z

3

The formula you're looking for is below. The issue stems from it not being a locked cell R5 will move down with your formula where $R$5 will stay constant on cell R5. You can also lock the column by $R5, or the row by R$5.

=SUMIFS(Payment_Details[Amount],Payment_Details[C_ID],M10,Payment_Details[Payment Status], "Paid")

or

=SUMIFS(Payment_Details[Amount],Payment_Details[C_ID],M10,Payment_Details[Payment Status], $R$5)

Since UNIQUE() is a spill formula you can even improve the formula with the below for the first instance of the formula (assuming M7), and it will auto fill for the whole spill range, so you don't even need to drag it as it updates.

=SUMIFS(Payment_Details[Amount],Payment_Details[C_ID],M7#,Payment_Details[Payment Status], $R$5)

Since it's a table you could also turn this into a pivot table and just do a sum of the amounts filtering out "Paid" and putting the C_ID in the rows.

edited Apr 24, 2024 at 21:03

answered Apr 24, 2024 at 20:53

Mark S.

2,8911 gold badge10 silver badges31 bronze badges

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

2 Comments

Paras Chopra Over a year ago

thanks @Mark_S. this is exactly what i was looking for. Another question , can we make a table out of this? I'm trying to insert table to this but its giving me error 'We can’t spill within a table'

Mark S. Over a year ago

You can't use a spill formula in a table, however as i mentioned you can skip this whole exercise and just have the original table feed a pivot table which will do all this grouping and summing automatically. Click the table, insert pivot table. Bring C_ID into the bottom-left box, the values into the bottom right, the status into the top-right and filter for "Paid" and it'll give you what you need and all you need to do is right-click and refresh. Don't forget to accept the answer is this answered your question

Austenite · Accepted Answer · 2024-10-31 04:40:14Z

0

A few months late for original poster, but as of September 23 2024 there are two new functions available in Excel for Microsoft 365: GROUPBY and PIVOTBY

These will do exactly what was requested. I have just used for the first time, and while at first glance they seem like "pivot table in a formula", I was able to do something not possible with a pivot table - dynamically filter by date greater than "TODAY() - X", which was very handy.

You can also add options to make minimum headers, totals, etc. The results are also directly accessible with normal formulas, not defaulting to and sometimes needing "GETPIVOTDATA". Overall, very cool.

answered Oct 31, 2024 at 4:40

Austenite

1

Collectives™ on Stack Overflow

Groupby in excel using only formulas

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related