groupings and addition

[guynextdoor]

Greetings,

Need your help please!
I have a spreadsheet with 5 columns:

Fund Code
Security ticker
Position
Local Market Value
Asset Type.

For the most part the fund consists of all equity, but holds cash and Short term cash equivalents. What I am trying to do is to group market value by two asset types: "Equity" and "Cash and Short terms" (including cash in various currencies, corporate bonds, money market etc).
So I need a formula that if asset type is NOT equity, take the market value and add to the "Cash and Short term" total.

Thanks a lot for your help!

Fund Ticker Position Local MV Asset Type



PORT12355 CPG 191,530 7,569,266 Equity
PORT12356 CSU 53,130 6,231,087 Equity
PORT12357 CTB 1,700 1,694,696 Corporate Bond
PORT12358 CTB 1,800 1,798,614 Corporate Bond
PORT12359 CVE 211,520 7,316,476 Equity
PORT12376 PRIMSR 5,160 5,159,853 Money Market
PORT12377 RY 167,380 9,540,660 Equity
PORT12391 WJA 151,730 2,725,072 Equity
PORT12392 YRI 360,910 7,081,052 Equity
PORT12393 522,423 522,423 Cash

[jake.masters]

Try this:

=If(F1<>"Equity",Market Value + Cash and Short Term, "")

If you posted a sample workbook, we could give you a more accurate forumula. I do not know where Market Val. and Cash & Short term are, so I just put the words in there.

[guynextdoor]

Hi Jake,
Thanks for your help!
Here is a sample spreadsheet.

One thing however, there will be numerous portfolios in this spreadsheet, not just one. The goal is to have CASH AND SHORT TERMS summed up for each individual portfolio.
Thanks!

[jake.masters]

Try this:

=SUMIFS(C:C,A:A, J1,E:E, "<>Equity")+ SUMIFS(D:D,A:A, J1,E:E, "<>Equity")

In conjunction with this sheet: It is in cell K1.

You can ignore everything in column F, I was just double checking my formula's math.

[jake.masters]

What you could end up doing is building a table with unique portfolio names starting in J1 and building down, such as

PORT1234
PORT4821
PORT1385

Then just drag the formula down.

[guynextdoor]

Jake,
Thanks for the suggestion. I have uploaded a new spreadsheet. Could you have a look?
Basically, Positions tab will be changing each morning, someone will copy and paste the new positions. So I dont want to have any formulas in this tab.
On the second tab- CASH, I'll have a list of all the funds and will only want to have a sum off all market values that are not equity from the first sheet broken by fund. Can this be done?
Many thanks!

[jake.masters]

I am getting an error message on the attachment. Could you try it again?

[jake.masters]

Also - what you just described is the formula I just sent you. I just put it in the same tab. All you need to do is move it to the second tab, and it will recalculate every day when they dump in the new information.

[guynextdoor]

help_final.xlsx
Does this one work?

[guynextdoor]

Also - what you just described is the formula I just sent you. I just put it in the same tab. All you need to do is move it to the second tab, and it will recalculate every day when they dump in the new information.

Jake,
Let me know if you can see the file now.
I'd appreciate your help and input!
Thank you.

[jake.masters]

Hi,

As I said, the formula will still work on your table. You can just drag down. If you give me a sample sheet with both MV and cash & short term, I'd be happy to build it for you. I believe yours only contains MV but not cash & ST. I can build it as is, but if we're going to have to go back and change it to include short term, I'd rather just skip that step. I'm happy to do either.

Just let me know.

Jake

Edit*

I just looked all the way through the data. Is the cash position the very bottom row for each portfolio?

[jake.masters]

Your formula is on the second page. Cash and SR are not included unless the numbers are in the same column.

[guynextdoor]

Your formula is on the second page. Cash and SR are not included unless the numbers are in the same column.

Jake! Thanks a million. THis is exactly what I had in mind!