Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Help please

Copper Contributor

Hello. I need help with building a sumifs formula. 

 

I have a very large data sheet full of invoices and payment due dates. I need to build a formula which would sum the amounts due, based on the invoice reference and due date. The problem is that one invoice reference can have 4 different due dates (trimestrial invoicing). So for example in column A I will have three different references: A, B and C, in column B I will have different due dates for each reference and in column C difference outstanding amount. Example below:

 

Invoice referenceDue dateOutstanding amount
A2023-09-2912
A2023-09-2913
A2023-09-2914
A2023-09-2913
B2023-12-0134
B2023-12-0164
B2023-07-15345
B2023-07-1534
C2023-08-30223
C2023-05-0565

 

Basically, I need to merge the outstanding amounts for the invoices with the same invoice reference and same due dates. Please help!

4 Replies

@Alexandra1910 

Did you consider using a pivot table? Some examples in the attached file.

Hello. Yes ofc I did but that doesn't help me. I need to have this data in more row. Using pivot table doesn't help :( I have 2000 invoice references that I need to compress according to the due date and invoice reference and they need to be in one row, as a normal table ...
best response confirmed by Alexandra1910 (Copper Contributor)
Solution

@Alexandra1910 But you can format a pt to display in tabular report lay-out and without subtotals. done that in the attached file. Wouldn't that work?

 

 

That would 100% work thanks a lot! I tried with the pivot table but couldn't figure it out. Thanks again :)
1 best response

Accepted Solutions
best response confirmed by Alexandra1910 (Copper Contributor)
Solution

@Alexandra1910 But you can format a pt to display in tabular report lay-out and without subtotals. done that in the attached file. Wouldn't that work?

 

 

View solution in original post