Forum Discussion
Help please
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!
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?
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 ...
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?
