Multiple entries in a cell

[Marquis76]

Hi,

I've been looking for hours for a solution - to no avail. I'm keeping track of my dividends in my stock portfolio. I have multiple accounts, at different banking institutions, some registered some non-registered. I'm using a code for each (i.e. 1-11). Sometimes, I buy the same stock in multiple accounts. Since dividend amounts are the same and paid on the same date for all accounts, I'm looking to register multiple values (i.e. accounts with the dividend-paying stock) under Account in my excel sheet, to avoid duplicating the dates in order to enter each value separately for each account. My sheet looks like this:

Date​	Dividends​	Account​
2017-03-31​	$21​	10,11​
2017-06-30​	$22​	10,11​
2017-09-17​	$22​	10,11​
2017-12-29​	$22​	10,11​
2018-03-29​	$22​	10,11​
2018-06-29​	$23​	10,11​
2018-09-28​	$23​	10,11​
2018-12-31​	$23​	10,11​
2019-03-29​	$23​	10,11​
2019-06-28​	$24​	10,11​
2019-09-30​	$24​	10,11​
2019-12-31​	$24​	10,11​
​	​	​
​	​	​
​	​	​

Then, each individual account sums up all dividends paid for this stock, per account. However, my problem is that I am unable to get excel to "read" cells with multiple values in them. Help! Thanks a million

 

[Peter_SSs]

From what you have shown and said, I am unsure what result(s) you expect and where they should be (or already are?). Can you clarify.

Also, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[Marquis76]

From what you have shown and said, I am unsure what result(s) you expect and where they should be (or already are?). Can you clarify.

Also, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

Hi Peter, I've updated my Excel version (2013). Want I want to do is find a way for excel to be able to SUMIF conditions in C3 are met, even when there are multiple variables in the same cell. In short, I receive the same amount of dividends for several accounts, on the same day. Hence the need to have multiple variables in the same cell.

Thanks a lot for your help!

 

[Marquis76]

Any help would be hugely appreciated!!

 

[Peter_SSs]

Any help would be hugely appreciated!!

Be patient - remember not everybody helping in the forum will be in your time-zone. Post #3 was around mid-night my time & the last one at 3:30am!

I still don't know what your actual expected results are. Are they shown in post #1 or not?

If they are, where (& remember C3 means nothing since your columns and rows are not labelled) and how did you get them manually?
If they are not shown already, can you show them & explain how you get them manually. Using XL2BB would be best, partly because then we can see rows/columns, any formulas, formatting etc.

 

[Marquis76]

Hi Peter. Thanks for the reminder about being patient Where do you live?

Ok, I'm cheating, it's not Excel but LibreOffice Calc, so the XL2BB trick won't work. Anyway to upload the file directly? If not, here's a better view. In the column "

RBC RRSP CAD/USD (10)", "Total Dividends" has the following formula "=SUMIF($Q12:$Q200,"10",$P12:$P200)", which obviously doesn't work because the cell containing the numbers associated with the accounts holding the security contains multiple values. I've highlighted in red all places of interest. I know that if I duplicated dates and amounts, I could have individual values for the accounts, but I don't want to do that otherwise my column will run for hundreds of cell all the way down....​

TD CAD M (1)​

TD USD M (2)​

TD CAD TFSA M (3)​

TD CAD A (4)​

TD USD A (5)​

TD CAD TFSA A (6)​

TD USD TFSA A (7)​

RBC CAD/USD (8)​

RBC TFSA CAD/USD (9)​

RBC RRSP CAD/USD (10)​

RBC RESP (11)​

Total invested​

$0.00​

Total invested​

$0.00​

Total invested​

$0.00​

Total invested​

$0.00​

Total invested​

$0.00​

Total invested​

$0.00​

Total invested​

$0.00​

Total invested​

$0.00​

Total invested​

$0.00​

Total invested​

$2,354.00​

Total invested​

$2,348.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Total proceeds​

$0.00​

Shares bought​

0​

Shares bought​

0​

Shares bought​

0​

Shares bought​

0​

Shares bought​

0​

Shares bought​

0​

Shares bought​

0​

Shares bought​

0​

Shares bought​

0​

Shares bought​

100​

Shares bought​

100​

Average SP bought​

#DIV/0!​

Average SP bought​

#DIV/0!​

Average SP bought​

#DIV/0!​

Average SP bought​

#DIV/0!​

Average SP bought​

#DIV/0!​

Average SP bought​

#DIV/0!​

Average SP bought​

#DIV/0!​

Average SP bought​

#DIV/0!​

Average SP bought​

#DIV/0!​

Average SP bought​

$23.54​

Average SP bought​

$23.48​

Shares sold​

0​

Shares sold​

0​

Shares sold​

0​

Shares sold​

0​

Shares sold​

0​

Shares sold​

0​

Shares sold​

0​

Shares sold​

0​

Shares sold​

0​

Shares sold​

0​

Shares sold​

0​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Average SP sold​

#DIV/0!​

Total dividends​

$0.00​

Total dividends​

$0.00​

Total dividends​

$0.00​

Total dividends​

$0.00​

Total dividends​

$0.00​

Total dividends​

$0.00​

Total dividends​

$0.00​

Total dividends​

$0.00​

Total dividends​

$0.00​

Total dividends​

$0.00​

Total dividends​

$0.00​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI exc. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Average ROI incl. Divs​

#DIV/0!​

Date​

Transaction​

Amount​

Shares​

Amount/Share​

Fees​

Capital gain (loss)​

Share Balance​

Change in ACB​

New ACB​

New ACB/shares​

ROI Per transaction​

Account​

Date​

Dividends​

Account​

​

​

2016-12-21​

buy​

$2,354.00​

100​

$23.54​

$9.99​

$0.00​

100​

$2,354.00​

$2,354.00​

$23.54​

​

10​

2017-03-31​

$21​

10,11​

​

​

2016-12-21​

buy​

$2,348.00​

100​

$23.48​

$9.99​

$0.00​

200​

$2,348.00​

$4,702.00​

$23.51​

0.00%​

11​

2017-06-30​

$22​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2017-09-17​

$22​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2017-12-29​

$22​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2018-03-29​

$22​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2018-06-29​

$23​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2018-09-28​

$23​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2018-12-31​

$23​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2019-03-29​

$23​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2019-06-28​

$24​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2019-09-30​

$24​

10,11​

​

​

​

​

​

​

#DIV/0!​

$9.99​

$0.00​

200​

$0.00​

$0.00​

$0.00​

0.00%​

​

2019-12-31​

$24​

10,11​

​

​

 

[Marquis76]

Actually the headers (account names and associated numbers) is all messed up in this copy/paste.

 

[Peter_SSs]

Where do you live?

Hover over my avatar and wait for the pop-up.

it's not Excel but LibreOffice Calc

I don't use LibreOffice Calc. Will all Excel formulas and/or macros work there?

Anyway to upload the file directly?

Not to the forum. You could upload to a file-share site like DropBox or use OneDrive, Google Drive etc and provide a shared link here.

=SUMIF($Q12:$Q200,"10",$P12:$P200)"

I cannot identify columns Q and P

 

[Marquis76]

Hi Peter. Visited Australia, loved it! Excel formulas work in LibreOffice Calc. Should be no problem. I uploaded the document! I highlighted the relevant cells in yellow. Example.xls

 

[Peter_SSs]

I uploaded the document!

I still don't know what your actual expected results are. Are they shown in post #1 the uploaded document or not?

If they are, where and how did you get them manually?
If they are not shown already, can you show them & explain how you get them manually?