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.
0
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.
2