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


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

4 Replies


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)

@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 :)