SOLVED

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 reference Due date Outstanding amount A 2023-09-29 12 A 2023-09-29 13 A 2023-09-29 14 A 2023-09-29 13 B 2023-12-01 34 B 2023-12-01 64 B 2023-07-15 345 B 2023-07-15 34 C 2023-08-30 223 C 2023-05-05 65

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

4 Replies

# Re: Help please

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

# Re: Help please

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

# Re: Help please

@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?

# Re: Help please

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

# Re: Help please

@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?