# Count unique days & add iteratively

#### spydey

##### Active Member
I have been racking my brain, trying to figure this out but have been unsuccessful to this point so far.
I hope I can make this simple to follow, understand, and to the point.
I have a formula I currently use which works correctly, but it does a cumulative sum. I am hoping to adjust it to do an iterative sum, which I explain below.

I have a single workbook with 2 worksheets:
• Main Data
• Transactions
The Main Data worksheet has the following columns in it:
• UID
• Start Date
• End Date
The UID only appears once in the Main Data, as would be assumed due to it being a UID.
The Start Date is always before or equal to the End Date.

The Transactions worksheet has the following columns in it:
• ID
• Type
• Start Date
• End Date
The ID can appear multiple times in the column.
The Start Date is always before or equal to the End Date.
For Example:
 ID Type Start Date End End 123 Apple 01/23/2019 07/14/2020 123 Pear 04/09/2019 05/21/2020 123 Carrot 07/24/2019 04/14/2021 789 Bread 01/01/2020 10/05/2020 ... ... ... ...

The formula works as follows:
• It is input in the Adj. End Date in the Main Data worksheet via CSE.
• It sums all unique days (difference of End Date & Start Date) from the Transaction worksheet where the Main Data:UID matches the Transactions:ID, and the Transactions:Start Date is after or equal to the Main Data:Start Date and the Transactions:Start Date is before or equal to the Transactions:End Date.
• It does not allow for duplicate days via overlap between the transactions.
• That sum is then added to the Main Data:End Date to get an Adj. End Date.
Here is the formula:

Excel Formula:
``=SUM(--(MMULT((ROW(INDIRECT([@[Start Date]]&":"&[@[End Date]]))>=TRANSPOSE(IF(Transactions[ID]=[@UID],Transactions[Start Date])))*(ROW(INDIRECT([@[Start Date]]&":"&[@[End Date]]))<=TRANSPOSE(IF(Transactions[ID]=[@UID],Transactions[End Date]))),Transactions[Start Date]^0)>0))+[@End Date]``

I am hoping to adjust the formula to be iterative. Not sure if I will need to add additional "helper" columns to make this work.

Basically, there are some situations where there might be several transactions for a single ID, but some of them start after the UID's End Date.
If we were to apply the number of days from those transactions that start prior to the UID's End date, we get an adjusted End date.
Then if we were to do it again, now using the "Adj. End Date" in place of the "End Date", it would adjust our "Adj. End Date" out even further".
So on and so forth until there are no more transactions that start before the most recently calculated Adj. End Date, or there are simply no more transactions for that UID.

Example:
• UID End Date is 06/05/2019.
• There are 3 transactions:
• 1st starts 04/10/2019 for 90 days
• 2nd starts 07/05/2019 for 50 days
• 3rd starts 12/15/2019 for 100 days
• Currently, due to the 2nd & 3rd transactions not starting prior to the UID End Date, they would be ignored if using the formula above, thus giving us our Adj. End Date of 06/05/2019 +90 days = 09/03/2019
• However, if we did an iterative calculation, then each transaction would be evaluated individually against the UID Adj. End Date:
• The first transaction would be evaluated against the UID End Date, found to meet the requirements, and added to the UID End Date giving us our Initial Adj. End Date of 09/03/2019
• The 2nd transaction would then be evaluated against the UID Initial Adj. End Date of 09/03/2019, found to meet the requirements, and thus added to the Initial Adj. End Date of 09/03/2019, giving us our Updated Adj, End Date of 10/23/2019.
• The 3rd transaction would then be evaluated against the Updated UID Adj. End Date of 10/23/2019, found to NOT meet the requirements and the evaluation would terminate.

The difficulty I see in doing this is:
• I never know how many transactions a single UID is going to have in the Transactions worksheet. It could be 1 or it could be hundreds.
• I would like to do it in a single formula, without any helper columns if possible (I doubt it is possible).
• If helper columns are needed, would they be dependant on how many transactions a UID has?
• Ensuring only unique days are counted, avoiding overlap between one transaction's end date and another transaction's start date.

Any thoughts, suggestions, ideas .... therapists (I have been working on this for quite a while and find myself at wits end ), you all could assist with is greatly appreciated!

-Spydey

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Replies
3
Views
101
Replies
11
Views
376
Replies
0
Views
76
Replies
11
Views
107
Replies
9
Views
392

1,137,124
Messages
5,679,743
Members
419,854
Latest member
marvin24

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back