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

sachi1982

New Member
Joined
May 14, 2015
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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.

DateSale
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​
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Check this and revert -

Book2
ABCDEFG
1Date Sale Daily Profit
211/1/2363 Profit Making days3
311/1/231174
411/2/23(12) If you use Excel 365
511/2/23(34) 11/1/2374
611/2/23(29) 11/2/23-112
711/2/23(37)(112)11/4/23-68
811/4/23(68)(68)11/6/2364
911/6/23(46) 11/7/2359
1011/6/2328 11/9/23-18
1111/6/2312 11/10/23-40
1211/6/237064
1311/7/2332 Profit Making days3
1411/7/2316
1511/7/2313
1611/7/239
1711/7/2325
1811/7/23(36)59
1911/9/233
2011/9/23(2)
2111/9/237
2211/9/23(22)
2311/9/23(4)(18)
2411/10/2341
2511/10/23(22)
2611/10/23(59)(40)
Sheet1
Cell Formulas
RangeFormula
F2F2=COUNTIFS(C2:C26,">0")
E5:E11E5=SORT(UNIQUE(A2:A26))
F5:F11F5=SUMIFS($B$2:$B$26,$A$2:$A$26,E5#)
F13F13=COUNTIFS(F5#,">0")
C2:C26C2=IFS(AND(A3<>"",A3=A2),"",TRUE,SUMIFS($B$2:$B$26,$A$2:$A$26,A2))
Dynamic array formulas.
 
Last edited:
Upvote 0
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’)
 
Upvote 0
Check this and revert -

Book2
ABCDEFG
1Date Sale Daily Profit
211/1/2363 Profit Making days3
311/1/231174
411/2/23(12) If you use Excel 365
511/2/23(34) 11/1/2374
611/2/23(29) 11/2/23-112
711/2/23(37)(112)11/4/23-68
811/4/23(68)(68)11/6/2364
911/6/23(46) 11/7/2359
1011/6/2328 11/9/23-18
1111/6/2312 11/10/23-40
1211/6/237064
1311/7/2332 Profit Making days3
1411/7/2316
1511/7/2313
1611/7/239
1711/7/2325
1811/7/23(36)59
1911/9/233
2011/9/23(2)
2111/9/237
2211/9/23(22)
2311/9/23(4)(18)
2411/10/2341
2511/10/23(22)
2611/10/23(59)(40)
Sheet1
Cell Formulas
RangeFormula
F2F2=COUNTIFS(C2:C26,">0")
E5:E11E5=SORT(UNIQUE(A2:A26))
F5:F11F5=SUMIFS($B$2:$B$26,$A$2:$A$26,E5#)
F13F13=COUNTIFS(F5#,">0")
C2:C26C2=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.
 
Upvote 0
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.
 
Upvote 0
T202311a.xlsm
ABD
1Date Sale 3
21-Nov-2363
31-Nov-2311
42-Nov-23-12
52-Nov-23-34
62-Nov-23-29
72-Nov-23-37
84-Nov-23-68
96-Nov-23-46
106-Nov-2328
116-Nov-2312
126-Nov-2370
137-Nov-2332
147-Nov-2316
157-Nov-2313
167-Nov-239
177-Nov-2325
187-Nov-23-36
199-Nov-233
209-Nov-23-2
219-Nov-237
229-Nov-23-22
239-Nov-23-4
2410-Nov-2341
2510-Nov-23-22
2610-Nov-23-59
2d
Cell Formulas
RangeFormula
D1D1=SUM(--(SUMIFS(rB,rA,UNIQUE(rA))>0))
Named Ranges
NameRefers ToCells
rA='2d'!$A$2:$A$26D1
rB='2d'!$B$2:$B$26D1
 
Upvote 1

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top