To count the number of profitable days when there are multiple entries for the same day with profits and losses

[sachi1982]

Hi all,

I would appreciate if you could please help me with an excel formula for below;
I need to count the number of profitable days when there are multiple entries for the same day with profits and losses. if the day's total sale is negative, need to consider that as a loss day. Need to count a day only when the day's total is positive.
for example, for the below data, I want a formula to say there are only 3 profitable days. How can I get that result from a formula.
please let me know.

Date	Sale
1/11/2023​	63​
1/11/2023​	11​
2/11/2023​	-12​
2/11/2023​	-34​
2/11/2023​	-29​
2/11/2023​	-37​
4/11/2023​	-68​
6/11/2023​	-46​
6/11/2023​	28​
6/11/2023​	12​
6/11/2023​	70​
7/11/2023​	32​
7/11/2023​	16​
7/11/2023​	13​
7/11/2023​	9​
7/11/2023​	25​
7/11/2023​	-36​
9/11/2023​	3​
9/11/2023​	-2​
9/11/2023​	7​
9/11/2023​	-22​
9/11/2023​	-4​
10/11/2023​	41​
10/11/2023​	-22​
10/11/2023​	-59​

 

[SanjayGMusafir]

Check this and revert -

Book2
	A	B	C	D	E	F	G
1	Date	Sale	Daily Profit
2	11/1/23	63			Profit Making days	3
3	11/1/23	11	74
4	11/2/23	(12)			If you use Excel 365
5	11/2/23	(34)			11/1/23	74
6	11/2/23	(29)			11/2/23	-112
7	11/2/23	(37)	(112)		11/4/23	-68
8	11/4/23	(68)	(68)		11/6/23	64
9	11/6/23	(46)			11/7/23	59
10	11/6/23	28			11/9/23	-18
11	11/6/23	12			11/10/23	-40
12	11/6/23	70	64
13	11/7/23	32			Profit Making days	3
14	11/7/23	16
15	11/7/23	13
16	11/7/23	9
17	11/7/23	25
18	11/7/23	(36)	59
19	11/9/23	3
20	11/9/23	(2)
21	11/9/23	7
22	11/9/23	(22)
23	11/9/23	(4)	(18)
24	11/10/23	41
25	11/10/23	(22)
26	11/10/23	(59)	(40)
Sheet1

Cell Formulas
Range	Formula
F2	F2	=COUNTIFS(C2:C26,">0")
E5:E11	E5	=SORT(UNIQUE(A2:A26))
F5:F11	F5	=SUMIFS($B$2:$B$26,$A$2:$A$26,E5#)
F13	F13	=COUNTIFS(F5#,">0")
C2:C26	C2	=IFS(AND(A3<>"",A3=A2),"",TRUE,SUMIFS($B$2:$B$26,$A$2:$A$26,A2))
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or 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. (Don’t forget to scroll down & ‘Save’)

 

[sachi1982]

Check this and revert -

Book2
	A	B	C	D	E	F	G
1	Date	Sale	Daily Profit
2	11/1/23	63			Profit Making days	3
3	11/1/23	11	74
4	11/2/23	(12)			If you use Excel 365
5	11/2/23	(34)			11/1/23	74
6	11/2/23	(29)			11/2/23	-112
7	11/2/23	(37)	(112)		11/4/23	-68
8	11/4/23	(68)	(68)		11/6/23	64
9	11/6/23	(46)			11/7/23	59
10	11/6/23	28			11/9/23	-18
11	11/6/23	12			11/10/23	-40
12	11/6/23	70	64
13	11/7/23	32			Profit Making days	3
14	11/7/23	16
15	11/7/23	13
16	11/7/23	9
17	11/7/23	25
18	11/7/23	(36)	59
19	11/9/23	3
20	11/9/23	(2)
21	11/9/23	7
22	11/9/23	(22)
23	11/9/23	(4)	(18)
24	11/10/23	41
25	11/10/23	(22)
26	11/10/23	(59)	(40)
Sheet1

Cell Formulas
Range	Formula
F2	F2	=COUNTIFS(C2:C26,">0")
E5:E11	E5	=SORT(UNIQUE(A2:A26))
F5:F11	F5	=SUMIFS($B$2:$B$26,$A$2:$A$26,E5#)
F13	F13	=COUNTIFS(F5#,">0")
C2:C26	C2	=IFS(AND(A3<>"",A3=A2),"",TRUE,SUMIFS($B$2:$B$26,$A$2:$A$26,A2))
Dynamic array formulas.

Thanks but I cannot add additional columns. I need one formula that shows me this info in a cell. I need to get that to a summary page so that by selecting the month, the user will get number of profitable sales days on that month.

 

[sachi1982]

What version of Excel are you using?

I suggest that you update your Account details (or 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. (Don’t forget to scroll down & ‘Save’)

Thanks for the info.

 

[Dave Patton]

try SUM(IF(SUMIFS(B2:B26,A2:A26,UNIQUE(A2:A26))>0,1))

 

[Dave Patton]

T202311a.xlsm
	A	B		D
1	Date	Sale		3
2	1-Nov-23	63
3	1-Nov-23	11
4	2-Nov-23	-12
5	2-Nov-23	-34
6	2-Nov-23	-29
7	2-Nov-23	-37
8	4-Nov-23	-68
9	6-Nov-23	-46
10	6-Nov-23	28
11	6-Nov-23	12
12	6-Nov-23	70
13	7-Nov-23	32
14	7-Nov-23	16
15	7-Nov-23	13
16	7-Nov-23	9
17	7-Nov-23	25
18	7-Nov-23	-36
19	9-Nov-23	3
20	9-Nov-23	-2
21	9-Nov-23	7
22	9-Nov-23	-22
23	9-Nov-23	-4
24	10-Nov-23	41
25	10-Nov-23	-22
26	10-Nov-23	-59
2d

Cell Formulas
Range	Formula
D1	D1	=SUM(--(SUMIFS(rB,rA,UNIQUE(rA))>0))

Named Ranges
Name	Refers To	Cells
rA	='2d'!$A$2:$A$26	D1
rB	='2d'!$B$2:$B$26	D1