Sum an array up until a match

Sevigny86

New Member
Joined
Apr 18, 2018
Messages
15
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!
wdq3a1olv2scD7Hm1
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top