Sum an array up until a match

[Sevigny86]

Good evening everyone,

I'm on a big excel project and I've run into another problem and I feel a little stumped. I'm have a table here that is pulling data from multiple sources. We will use the SGC-US Dividends example (AK:AQ)

Column AQ is pulling the history of our trades with that stock, with the quantity traded in AL. Then columns AM, AN & AP pulls dividend data from an online database. To see if we are eligible to collect the said dividend and for how many shares, I need to verify how many shares we held at that point in time; Quantity on Record (AO). The formula I used is as follows (can also be found in the image linked, as well as the data). Its a little round about way but I was wondering if anyone had any suggestions.

Formula: =IF(AM4="","",IFERROR(IF(IF((LOOKUP(2,1/(1-ISBLANK(AK$3:AK$200)),AK$3:AK$200)<AM4)=FALSE,SUM(AL$3:AL4),LOOKUP(2,1/(1-ISBLANK(AK$3:AK$200)),AK$3:AK$200)<AM4),AO3),""))

Image: https://photos.app.goo.gl/wdq3a1olv2scD7Hm1

Currently AO is not calculating properly; its passing everything and making it to the end which states taking the quantity of the previous cell. But I would need that cell to equate the sum of the transactions up until that point in time. I would ideally like to have it done without adding another column. The reason for this is because there are about 11 tables like this side by side, that I will then want to search and order all received dividends by date (Bonus if someone can help with this, but I plan to make a second thread for this problem!).

Please let me know if I'm unclear, but any help on this would be greatly appreciated!

 

[Sevigny86]

Just to clarify a bit,

In the image with SGC-US Dividends, the values in column AO should be as follows:

AO3 = 0
AO4 = 1425
AO5 = 1425
AO6 = 1425
AO7 = 1425
AO8 = 1425

Ant then technically when the next dividend will be paid out, it should be AO9 = 0

I've tried a few other formulas since but I have not been successful. Technically It would just need to sum column AL for the rows where the corresponding AK date is smaller than the check date in column AM as we go down. The problem I have is theyre two sets of arrays, and are mismatched in size.

Thanks again!

 

[Sevigny86]

Resolved using Sumif function. The problem was the dates being input from the online database were not recognized as numbers, but rather text so all formulas failed until this was corrected.