Hi Every one,
I need to create a sumifs formula to capture information based on a number of pieces of criteria. Two of those criteria are very easy to gather, but the final piece is much more difficult to capture accurately.
1) The sum of values needs to be associated with values present in a specific row which is easily accomplished by setting the sum range to an index match within the data tab.
2) Only values that have "Amount Due" listed in row 2 of the column the value is in should be considered which is also accomplished quickly by setting one criteria to search that range for that criteria.
3) Contained within the horizontal area that has the value to be summed is the date that it is expected to be collected. This date is 2 cells to the right of the value to be included in the sum and needs to be the same month and year as the month represented in the sum table
Is it possible to get a formula together to make this process automated? Let me know if you think this is possible and how you might approach it or if I need to completely reformat my table in some way:
I need to create a sumifs formula to capture information based on a number of pieces of criteria. Two of those criteria are very easy to gather, but the final piece is much more difficult to capture accurately.
1) The sum of values needs to be associated with values present in a specific row which is easily accomplished by setting the sum range to an index match within the data tab.
2) Only values that have "Amount Due" listed in row 2 of the column the value is in should be considered which is also accomplished quickly by setting one criteria to search that range for that criteria.
3) Contained within the horizontal area that has the value to be summed is the date that it is expected to be collected. This date is 2 cells to the right of the value to be included in the sum and needs to be the same month and year as the month represented in the sum table
Is it possible to get a formula together to make this process automated? Let me know if you think this is possible and how you might approach it or if I need to completely reformat my table in some way:
| Book1 | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | $ 13,123,596.55 | $ 19,500.00 | Aug-21 | $ 26,804,111.22 | $ 5,179,549.10 | Mar-22 | $ 13,123,596.55 | $ 19,500.00 | Aug-21 | ||||||||||||
| 2 | Project Number | Total Billing | Amount Due | Comments | Expected/Committed Payment Date | Payment Receipt Date | Paid? | Total Billing | Amount Due | Expected Payment Date/Comments | Expected/Committed Payment Date | Payment Receipt Date | Paid? | Total Billing | Amount Due | Comments | Expected/Committed Payment Date | Payment Receipt Date | Paid? | ||
| 3 | 1 | $ 23,154.80 | $ - | $ 23,154.80 | 5/17/2022 | x | $ - | $ 23,154.80 | 6/25/2022 | $ 23,154.80 | $ - | $ 23,154.80 | 5/1/2022 | x | |||||||
| 4 | 2 | $ - | $ - | $ - | |||||||||||||||||
| 5 | 3 | $ - | $ 6,197.97 | $ 6,197.97 | 5/15/2022 | $ - | |||||||||||||||
| 6 | 4 | $ - | $ - | $ - | |||||||||||||||||
| 7 | 5 | $ - | $ - | $ - | |||||||||||||||||
Data Sheet | |||||||||||||||||||||
| May 2022 Active Projects List and Open Billings Cash Flow Working Copy.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Project Number | Outstanding Progress Billing Total | May-22 | Jun-22 | Jul-22 | Aug-22 | Sep-22 | Oct-22 | ||||||||
| 2 | Expected Collection Total | Actual Receipts | Expected Collection Total | Actual Receipts | Expected Collection Total | Actual Receipts | Expected Collection Total | Actual Receipts | Expected Collection Total | Actual Receipts | Expected Collection Total | Actual Receipts | ||||
| 3 | 1 | #N/A | #N/A | |||||||||||||
| 4 | 2 | #N/A | ||||||||||||||
| 5 | 3 | #N/A | ||||||||||||||
| 6 | 4 | #N/A | ||||||||||||||
Progress Billing Cash Flow | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3 | C3 | =SUMIFS(INDEX('Active Projects & Cash Flow'!$1:$653,MATCH('Progress Billing Cash Flow'!A3,'Active Projects & Cash Flow'!$A:$A,0),),'Active Projects & Cash Flow'!2:2,"Amount Due",INDEX('Active Projects & Cash Flow'!1:653,,MATCH("Expected/Committed Payment Date",'Active Projects & Cash Flow'!2:2,0)),'Progress Billing Cash Flow'!C1) |
| B3:B6 | B3 | =SUMIF('Active Projects & Cash Flow'!$2:$2,"Amount Due",INDEX('Active Projects & Cash Flow'!$1:$653,MATCH('Progress Billing Cash Flow'!A3,'Active Projects & Cash Flow'!$A:$A,0),)) |
