Summing a variable amount of cells by date

[steved4545]

I am wanting to create a daily profit and loss number by date. I may have 5 trades per day or I may have 25 trades per day and I want to create a summary page for each days profit/loss and not sure how to do that. Any suggestions? I am a novice excel user. I have tried messing around with =SUMIFS but haven't been successful.

 

[Joe4]

Welcome to the Board!

It all depends on your data structure, and what you would like your results to look like. Can you show us those two things?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[steved4545]

Welcome to the Board!

It all depends on your data structure, and what you would like your results to look like. Can you show us those two things?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

When I try to download the addin, it tells me I can't because I am in protective view. I created the spreadsheet and now sure how to get out of Protective view. I have no yellow bar asking me to enable editing. Here is a quick snipit of my data. I am wanting to add the last columns for each day of the week. Sometimes there are 7 trades sometimes there are 25 trades by date. The output I am just looking for the numerical sum of the profits and loss

4/1/2024​	Sell to Open Short	150/150	24.855/--	3000​	37.5​	--
4/1/2024​	Buy to Close Short	150/150	24.799/--	3000​	37.5​	337.96017​
4/1/2024​	Sell to Open Short	150/150	24.77/--	3000​	37.5​	--
4/1/2024​	Buy to Close Short	150/150	24.761/--	3000​	37.5​	54.50141​
4/1/2024​	Sell to Open Short	150/150	24.858/--	3000​	37.5​	--
4/1/2024​	Buy to Open Long	150/150	24.894/--	3000​	37.5​	--
4/1/2024​	Buy to Close Short	150/150	24.894/--	3000​	37.5​	-217.23389​
4/1/2024​	Sell to Close Long	150/150	24.955/--	3000​	37.5​	367.55845​
4/1/2024​	Buy to Open Long	150/150	24.869/--	3000​	37.5​	--
4/1/2024​	Sell to Close Long	150/150	24.78/24.78	3000​	37.5​	-536.8129​
4/1/2024​	Buy to Open Long	100/100	24.697/24.7	2000​	25​	--
4/1/2024​	Sell to Close Long	100/100	24.96/24.96	2000​	25​	1064.90667​
4/1/2024​	Buy to Open Long	200/200	24.928/--	4000​	50​	--
4/1/2024​	Sell to Close Long	200/200	24.96/--	4000​	50​	256.73941​
4/1/2024​	Buy to Open Long	200/200	24.91/24.91	4000​	50​	--

 

[Joe4]

I think you are going to need to explain it a bit more.
What exactly is being totalled here? The 3rd and 4th columns don't even look like valid numeric columns.

I think it would be best if you can walk us through an example based on your data, telling us what you want to end up with for that particular example and explain exactly how you arrived at that particular value. Remember, while you have all this data in front of you, and in your mind you know exactly what you are trying to accomplish, we don't. It is all brand new to us and all that we know is what you decide to share with us. So be as detailed as possible.

The key to getting a good answer is asking a good complete question. Here is a link with a bunch of tips on how to do that: Guidelines

 

[Cubist]

I think this is what you're looking for.

Book1
	A	B	C	D	E	F	G	H	I	J
1	4/1/2024	Sell to Open Short	150/150	24.855/--	3000	37.5	0.00		Date	SUM
2	4/1/2024	Buy to Close Short	150/150	24.799/--	3000	37.5	337.96		4/1/2024	1,326.46
3	4/1/2024	Sell to Open Short	150/150	24.77/--	3000	37.5	0.00
4	4/1/2024	Buy to Close Short	150/150	24.761/--	3000	37.5	54.50
5	4/1/2024	Sell to Open Short	150/150	24.858/--	3000	37.5	0.00
6	4/1/2024	Buy to Open Long	150/150	24.894/--	3000	37.5	0.00
7	4/1/2024	Buy to Close Short	150/150	24.894/--	3000	37.5	-217.00
8	4/1/2024	Sell to Close Long	150/150	24.955/--	3000	37.5	367.00
9	4/1/2024	Buy to Open Long	150/150	24.869/--	3000	37.5	0.00
10	4/1/2024	Sell to Close Long	150/150	24.78/24.78	3000	37.5	-536.00
11	4/1/2024	Buy to Open Long	100/100	24.697/24.7	2000	25	0.00
12	4/1/2024	Sell to Close Long	100/100	24.96/24.96	2000	25	1064.00
13	4/1/2024	Buy to Open Long	200/200	24.928/--	4000	50	0.00
14	4/1/2024	Sell to Close Long	200/200	24.96/--	4000	50	256.00
15	4/1/2024	Buy to Open Long	200/200	24.91/24.91	4000	50	0.00
Sheet5

Cell Formulas
Range	Formula
J2	J2	=SUMIF($A$1:$A$15,I2,$G$1:$G$15)

 

[steved4545]

I think you are going to need to explain it a bit more.
What exactly is being totalled here? The 3rd and 4th columns don't even look like valid numeric columns.

I think it would be best if you can walk us through an example based on your data, telling us what you want to end up with for that particular example and explain exactly how you arrived at that particular value. Remember, while you have all this data in front of you, and in your mind you know exactly what you are trying to accomplish, we don't. It is all brand new to us and all that we know is what you decide to share with us. So be as detailed as possible.

The key to getting a good answer is asking a good complete question. Here is a link with a bunch of tips on how to do that: Guidelines

I am wanting to add all of the numbers in the last column, column 7 on this set of data. I have trades on every day of the month. I am looking for a formula to total all of the values in column 7 if they were obtained each different date in column 1. Here is data for two dates. In this example the result I am looking for for April 1, 2024 would be 9949.72 and on April 2, 2024 I would want to see the value of -174.08. I would want the summary by day on a second sheet with just listing the day and the result for the day.

4/1/2024 14:11​	Sell to Close Long	200/200	24.413/--	4000​	50​	3069.37282​
4/1/2024 14:56​	Buy to Open Long	25/25	24.574/--	500​	6.25​	--
4/1/2024 15:27​	Sell to Close Long	25/25	24.78/--	500​	6.25​	209.57109​
4/1/2024 16:45​	Sell to Open Short	250/250	24.975/--	5000​	62.5​	--
4/1/2024 17:46​	Buy to Close Short	150/150	24.398/--	3000​	37.5​	3465.46547​
4/1/2024 17:48​	Buy to Close Short	100/100	24.435/--	2000​	25​	2162.16216​
4/1/2024 20:58​	Buy to Open Long	150/150	24.589/--	3000​	37.5​	--
4/1/2024 21:48​	Sell to Close Long	150/150	24.76/--	3000​	37.5​	1043.14938​
4/2/2024 4:13​	Sell to Open Short	200/200	24.049/--	4000​	50​	--
4/2/2024 4:26​	Buy to Close Short	200/200	24.025/--	4000​	50​	199.5925​
4/2/2024 4:52​	Sell to Open Short	200/200	23.791/--	4000​	50​	--
4/2/2024 4:56​	Buy to Close Short	200/200	23.743/--	4000​	50​	403.51393​
4/2/2024 4:57​	Sell to Open Short	200/200	23.675/--	4000​	50​	--
4/2/2024 5:00​	Buy to Close Short	200/200	23.767/--	4000​	50​	-777.19113​

 

[steved4545]

I think this is what you're looking for.

Book1
	A	B	C	D	E	F	G	H	I	J
1	4/1/2024	Sell to Open Short	150/150	24.855/--	3000	37.5	0.00		Date	SUM
2	4/1/2024	Buy to Close Short	150/150	24.799/--	3000	37.5	337.96		4/1/2024	1,326.46
3	4/1/2024	Sell to Open Short	150/150	24.77/--	3000	37.5	0.00
4	4/1/2024	Buy to Close Short	150/150	24.761/--	3000	37.5	54.50
5	4/1/2024	Sell to Open Short	150/150	24.858/--	3000	37.5	0.00
6	4/1/2024	Buy to Open Long	150/150	24.894/--	3000	37.5	0.00
7	4/1/2024	Buy to Close Short	150/150	24.894/--	3000	37.5	-217.00
8	4/1/2024	Sell to Close Long	150/150	24.955/--	3000	37.5	367.00
9	4/1/2024	Buy to Open Long	150/150	24.869/--	3000	37.5	0.00
10	4/1/2024	Sell to Close Long	150/150	24.78/24.78	3000	37.5	-536.00
11	4/1/2024	Buy to Open Long	100/100	24.697/24.7	2000	25	0.00
12	4/1/2024	Sell to Close Long	100/100	24.96/24.96	2000	25	1064.00
13	4/1/2024	Buy to Open Long	200/200	24.928/--	4000	50	0.00
14	4/1/2024	Sell to Close Long	200/200	24.96/--	4000	50	256.00
15	4/1/2024	Buy to Open Long	200/200	24.91/24.91	4000	50	0.00
Sheet5

Cell Formulas
Range	Formula
J2	J2	=SUMIF($A$1:$A$15,I2,$G$1:$G$15)

 

[steved4545]

Yes, that is what I am looking for and I would like the result on a separate sheet so I am only looking at the summary by date. I have many lines of data for each date so it would have to check all of the results for April 1st, then April 2nd and so on. I gave a second set of data to a previous reply that carried over to two days. In this example I would want the summary total of each date; for April 1, 2024 would be 9949.72 and on April 2, 2024 I would want to see the value of -174.08.

4/1/2024 14:11​	Sell to Close Long	200/200	24.413/--	4000​	50​	3069.37282​
4/1/2024 14:56​	Buy to Open Long	25/25	24.574/--	500​	6.25​	--
4/1/2024 15:27​	Sell to Close Long	25/25	24.78/--	500​	6.25​	209.57109​
4/1/2024 16:45​	Sell to Open Short	250/250	24.975/--	5000​	62.5​	--
4/1/2024 17:46​	Buy to Close Short	150/150	24.398/--	3000​	37.5​	3465.46547​
4/1/2024 17:48​	Buy to Close Short	100/100	24.435/--	2000​	25​	2162.16216​
4/1/2024 20:58​	Buy to Open Long	150/150	24.589/--	3000​	37.5​	--
4/1/2024 21:48​	Sell to Close Long	150/150	24.76/--	3000​	37.5​	1043.14938​
4/2/2024 4:13​	Sell to Open Short	200/200	24.049/--	4000​	50​	--
4/2/2024 4:26​	Buy to Close Short	200/200	24.025/--	4000​	50​	199.5925​
4/2/2024 4:52​	Sell to Open Short	200/200	23.791/--	4000​	50​	--
4/2/2024 4:56​	Buy to Close Short	200/200	23.743/--	4000​	50​	403.51393​
4/2/2024 4:57​	Sell to Open Short	200/200	23.675/--	4000​	50​	--
4/2/2024 5:00​	Buy to Close Short	200/200	23.767/--	4000​	50​	-777.19113​

 

[Joe4]

I would recommend looking at creating Pivot Tables. There are lot of tutorials on that out on the internet (videos, step-by-step documents, etc - whatever your favorite way to learn is).
Just do a Google search on "Create a Pivot Table in Excel", and you should find tons of tutorials on it.