Sumproduct with substraction and ifs

How do i do this?
(in file)

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand.

In B11 copied down:

=SUMIFS($C$2:$C$6,$B$2:$B$6,"Buy",$A$2:$A$6,A11)-SUMIFS($C$2:$C$6,$B$2:$B$6,"Sell",$A$2:$A$6,A11)

You will need to change commas to semi-colons for your Dutch locale:

=SUMIFS($C$2:$C$6;$B$2:$B$6;"Buy";$A$2:$A$6;A11)-SUMIFS($C$2:$C$6;$B$2:$B$6;"Sell";$A$2:$A$6;A11)

See post #3.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

What would you expect the results to be for your sample?

Maybe this:

=SUMIFS($C$2:$C$6,$B$2:$B$6,"Buy",$A$2:$A$6,A11)/SUMIFS($C$2:$C$6,$B$2:$B$6,"Buy")

Format the result cell as percentage.

Excel 2016 (Windows) 32 bit

	A	B	C
11	Eth	3	98.68%
12	Bitcoin	0.035	1.32%

Sheet: Sheet3

Or maybe you mean this:

=SUMIFS($B$11:$B$12,$A$11:$A$12,A11)/SUM($B$11:$B$12)

Try this:

=IF(G3="","",SUMIFS($F$3:$F$31,$B$3:$B$31,B3)/SUM($F$3:$F$31))

It actually should be focust on the dollar instead of the coins because they all have a different value. then the % wont work

more like

Please Login or Register  to view this content.

If you've resolved it, please mark the thread as solved.

OK - sorry, I'm not following the newly attached sheet, then, and I am running short of time today. I'll leave it for someone else to have a look at.

Ok
This is what should happen

Looking at colomn I (ratio, right side) you see 20% bitcoin meaning that this is what i'm aiming for to buy.
Colomn H (ratio, left side) is the % that i currently have of bitcoin.

Colomn F (holdings) is what works right now and it tells you how many coins i have on the other tab (ledger) per coin.
Colomn G (also holdings) should calculate how many coins i should buy if i want to have colomn H (ratio left) on sync with colomn J (ratio, right)

So it should look like:
0.195 / 0.20 19 / 20%


Then the ROI should be 1609%
and the margin 94,2%

Post deleted

So it should look like:
0.195 / 0.20 19 / 20%

It may help someone propose a solution if you could tell us how you arrived at 19?

Looking at the file attached to post #1 it would seem that the total amount paid for bitcoin is $600, however I don't know what the total invested for bitcoin would be.
Please remember that you are the expert on the topic of investments, we are here to help you convert your calculations on the Portfolio sheet into Excel formulas/code.
Let us know if you have any questions.

The formula for H3 is

Please Login or Register  to view this content.

But it should only calculate the dollar amount and not the btc amount in it.

P3 now is

Please Login or Register  to view this content.

But K3 will be removed and needs to be replaced with the numbers of total current bitcoin on the tab ledger. How?

Same goes for
R3

Please Login or Register  to view this content.

Replace K3 with only bitcoin from ledger.