COUNTIFS function help please

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
Hi all,

This is my first post here:

I'm attempting to identify the smallest number within a column. I have a series of sheets with a column that's filled with profit from forex trades.
The formula is here

=MIN(IF(O4:O254<0,MIN(O4:O254),O4:O254))

It is working fine to give the largest loss

I have an overview sheet also which has more data in the columns and I need to exclude some figures from the calculation, they are giving skewed results

=COUNTIFS(F2:F252,"isnumber", N2:N252, MIN(IF(N2:N252<0,MIN(N2:N252),N2:N252)))

Please note that the N/O column difference is only due to the way the information is displayed, the

Essentially I am trying to exclude the any rows from the calculation if the cell in F is blank.

The current formula returns a $0.00 value but that's not correct.

Any assistance would be wonderful and very appreciated.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
Welcome to Mr. Excel.

Do you want to count something or just find the smallest in column N without considering blanks in column F?
Does this do what you want?

Code:
=MIN(IF(F2:F252<>"",N2:N252,""))
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
Hey there,

Cheers for the quick reply!

That formula unfortunately gives the values I'm trying to exclude which is super odd.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
My formula should be selecting the values in column N where the values in column F are NOT blank.

Book7
FGHIJKLMNO
1
2317
3-473
4384
58-9
63
7225
8-22-5
9498
10-5-8
11205
12-2
13-80
14810
15313
16415
17-10
184
1950-10
2069
21188
22299
23
-10
Sheet2
Cell Formulas
RangeFormula
O23O23=MIN(IF(F2:F252<>"",N2:N252,""))
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
Yes, I can see, I"m confused as to why they are still coming up too. I wonder if I might post something for you?

Copy of Andrew_Murray_Trade_Results_2020.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1TicketOpen TimeTypeSizeItemPriceS / LT / PClose TimePrice2CommissionTaxesSwapProfitTrade4000
22213919162020.05.06 00:29:47sell0.03eurnzd-e1.791991.814310.000002020.05.06 00:31:351.79421400-4.03
1 - eurnzd-e
3995.97
32213919122020.05.06 00:29:47sell0.03eurnzd-e1.791991.814311.773632020.05.06 00:31:341.79421400-4.04
6 - eurnzd-e
3991.93
Updated when trade is closed
42213924122020.05.06 00:38:04sell0.03eurnzd-e1.792231.814540.000002020.05.06 00:38:311.79424400-3.65
3 - eurnzd-e
3988.28
Realized Profit/Loss
770536.2
Profit/Loss of all closed trades
-4.03
52213924112020.05.06 00:38:04sell0.03eurnzd-e1.792231.814541.773862020.05.06 00:38:301.79424400-3.64
4 - eurnzd-e
3984.64
Gross profit
617.83
Total profit of all won trades
-8.06
62213924182020.05.06 00:38:46sell0.03gbpjpy-e132.515133.8470.0002020.05.06 00:39:37132.666400-4.25
1 - gbpjpy-e
3980.39
Gross loss
789.27
Total loss of all lost trades
-12.1
72213924172020.05.06 00:38:46sell0.03gbpjpy-e132.515133.847131.4792020.05.06 00:39:36132.666400-4.26
2 - gbpjpy-e
3976.13
Expected payoff
-2.06554
Average trade outcome
-15.75
82216104502020.05.11 23:13:38buy0.05audjpy-e69.86169.00770.7152020.05.13 19:18:0969.007400-39.92
1 - audjpy-e
3936.21
Profit factor
0.782787
Gross profit / gross loss
-19.39
92216104512020.05.11 23:13:39buy0.05audjpy-e69.86169.0070.0002020.05.13 19:18:0969.007400-39.91
2 - audjpy-e
3896.3
Number of closed tradesPer Year
83
-23.64
102216292502020.05.12 00:01:07balanceFinancing0-0.16
 
3896.14
Profit trades
31
-27.9
112218096392020.05.13 00:01:35balanceFinancing0-0.16
 
3895.98
Loss trades
52
-67.82
122219757062020.05.14 23:32:09sell0.07nzdcad-e0.841510.849530.000002020.05.19 23:50:010.84721400-28.77
1 - nzdcad-e
3867.21
Largest profit trade
$94.11
-107.73
132219757052020.05.14 23:32:09sell0.07nzdcad-e0.841510.849530.834132020.05.19 23:50:000.84722400-28.82
2 - nzdcad-e
3838.39
Largest loss trade
-$100.00
-107.89
142220254812020.05.15 23:31:25sell0.03gbpjpy-e129.765131.203128.3912020.05.18 18:23:07131.203400-40.39
3 - gbpjpy-e
3798
Average profit trade
$19.93
-108.05
152220254822020.05.15 23:31:26sell0.03gbpjpy-e129.765131.2030.0002020.05.18 18:23:07131.203400-40.39
4 - gbpjpy-e
3757.61
Average loss trade
-$15.18
-136.82
162220255382020.05.15 23:40:17sell0.05nzdchf-e0.576280.584110.569152020.05.18 16:41:360.58412400-40.64
1 - nzdchf-e
3716.97
Long trades
30
-165.64
172220255392020.05.15 23:40:18sell0.05nzdchf-e0.576280.584110.000002020.05.18 16:41:360.58412400-40.64
2 - nzdchf-e
3676.33
Short trades
22
-206.03
182220471072020.05.16 00:01:53balanceFinancing0-0.92
 
3675.41
Long trades won
24
-246.42
192221012802020.05.19 00:01:31balanceFinancing0-0.32
 
3675.09
Short trades won
6
-287.06
202221284802020.05.19 23:46:27buy0.03audjpy-e70.37868.9360.0002020.05.21 23:33:5270.6154006.57
3 - audjpy-e
3681.66
Max. consecutive wins
6
Longest winning streak (number of trades)
-327.7
212221284792020.05.19 23:46:27buy0.03audjpy-e70.37768.93671.3402020.05.21 23:33:5170.6154006.61
4 - audjpy-e
3688.27
Max. consecutive losses
1
Longest losing streak (number of trades)
-328.62
222221284832020.05.19 23:47:16buy0.03cadjpy-e77.27379.8180.0002020.06.09 12:22:5580.01040075.74
1 - cadjpy-e
3764.01
Max. consecutive profit
57.32
Largest winning streak (total profit)
-328.94
232221284822020.05.19 23:47:16buy0.03cadjpy-e77.27375.97778.1372020.05.26 21:29:1978.13940024.02
2 - cadjpy-e
3788.03
Max. consecutive loss
-328.94
Largest losing streak (total loss)
-322.37
242221284912020.05.19 23:49:08buy0.03eurusd-e1.092501.080281.100652020.05.21 15:25:301.1006540024.45
1 - eurusd-e
3812.48
Paid commissions
220
-315.76
252221284942020.05.19 23:49:09buy0.03eurusd-e1.092501.092600.000002020.05.22 08:04:431.092604000.3
2 - eurusd-e
3812.78
-240.02
262221503252020.05.20 00:01:34balanceFinancing04-0.48
 
3812.3
Win Rate
37.35%
-216
272221764432020.05.20 22:47:13buy0.03audusd-e0.660110.685940.000002020.06.09 12:22:560.6914840094.11
1 - audusd-e
3906.41
-191.55
282221764422020.05.20 22:47:13buy0.03audusd-e0.660110.647460.668552020.06.01 03:18:310.6685640025.35
2 - audusd-e
3931.76
-191.25
292221764552020.05.20 22:49:17sell0.02usdcad-e1.389481.408241.377222020.05.26 20:25:011.3772240017.72
1 - usdcad-e
3949.48
-100
-191.73
302221764562020.05.20 22:49:18sell0.02usdcad-e1.389481.356960.000002020.06.09 12:22:581.3480940061.11
2 - usdcad-e
4010.59
-97.62
312221985372020.05.21 00:01:48balanceFinancing4-2.68
 
4007.91
-72.27
322222235572020.05.21 23:39:48buy0.03audchf-e0.637260.637360.000002020.05.27 17:47:110.637354000.28
1 - audchf-e
4008.19
-54.55
332222235562020.05.21 23:39:48buy0.03audchf-e0.637260.626170.644652020.05.27 10:47:140.6446640022.78
2 - audchf-e
4030.97
6.56
342222235792020.05.21 23:44:22buy0.03nzdchf-e0.594300.594400.000002020.05.29 18:01:400.594394000.28
3 - nzdchf-e
4031.25
3.88
352222235772020.05.21 23:44:22buy0.03nzdchf-e0.594300.583370.601592020.05.26 18:00:450.6016040022.52
4 - nzdchf-e
4053.77
4.16
362222375342020.05.22 00:01:27balanceFinancing0-0.46
 
4053.31
26.94
372222769442020.05.23 00:01:31balanceFinancing0-0.24
 
4053.07
27.22
382223258612020.05.26 00:01:34balanceFinancing0-0.37
 
4052.7
49.74
392223760162020.05.27 00:01:31balanceFinancing0-0.25
 
4052.45
49.28
402224020702020.05.27 22:50:09buy0.04cadchf-e0.703510.695210.000002020.05.29 17:57:320.69518400-34.86
1 - cadchf-e
4017.59
49.04
412224020692020.05.27 22:50:09buy0.04cadchf-e0.703510.695210.709052020.05.29 17:57:320.69518400-34.87
2 - cadchf-e
3982.72
48.67
422224250192020.05.28 00:01:51balanceFinancing0-0.63
 
3982.09
48.42
432224519502020.05.28 23:30:54buy0.02gbpusd-e1.231951.215050.000002020.06.01 05:53:321.240410016.92
1 - gbpusd-e
3999.01
13.56
442224519492020.05.28 23:30:54buy0.02gbpusd-e1.231951.215051.243222020.06.01 05:53:321.2404140016.92
2 - gbpusd-e
4015.93
-21.31
452224519572020.05.28 23:32:39sell0.04usdchf-e0.964140.973330.958332020.06.01 05:53:210.9601040016.84
1 - usdchf-e
4032.77
-21.94
462224519582020.05.28 23:32:40sell0.04usdchf-e0.964140.973330.000002020.06.01 05:53:220.9601040016.83
2 - usdchf-e
4049.6
-5.02
472224739622020.05.29 00:02:04balanceFinancing0-0.92
 
4048.68
11.9
482225254872020.05.30 00:01:40balanceFinancing0-0.37
 
4048.31
28.74
492225392172020.06.01 09:11:07balanceADJUSTMENT0-100
 
3948.31
45.57
502225392272020.06.01 09:11:43balanceADJUSTMENT0-20
 
3928.31
44.65
512225786202020.06.02 00:01:32balanceFinancing0-0.17
 
3928.14
44.28
522226095022020.06.02 23:48:51buy0.04audcad-e0.932240.932540.000002020.06.03 11:08:350.932524000.82
1 - audcad-e
3928.96
-55.72
532226095012020.06.02 23:48:51buy0.04audcad-e0.932240.920990.939742020.06.03 04:07:130.9397640022.2
2 - audcad-e
3951.16
-75.72
542226095052020.06.02 23:48:59buy0.03audchf-e0.663850.664150.000002020.06.09 10:18:150.664134000.88
3 - audchf-e
3952.04
-75.89
552226095042020.06.02 23:48:59buy0.03audchf-e0.663850.651790.671892020.06.05 15:58:120.6719040024.93
4 - audchf-e
3976.97
-75.07
562226095132020.06.02 23:50:46sell0.02euraud-e1.619271.644930.000002020.06.04 23:39:091.63130400-16.8
1 - euraud-e
3960.17
-52.87
572226095122020.06.02 23:50:46sell0.02euraud-e1.619271.644931.602732020.06.04 23:39:081.63138400-16.91
2 - euraud-e
3943.26
-51.99
582226095152020.06.02 23:51:16sell0.02eurnzd-e1.753681.745070.000002020.06.10 01:29:001.7450740011.14
5 - eurnzd-e
3954.4
-27.06
592226095142020.06.02 23:51:16sell0.02eurnzd-e1.753681.781261.736062020.06.05 15:32:141.7360640022.82
6 - eurnzd-e
3977.22
-43.86
602226095192020.06.02 23:52:08buy0.02gbpchf-e1.208161.208460.000002020.06.09 11:26:131.208454000.6
1 - gbpchf-e
3977.82
-60.77
612226095182020.06.02 23:52:08buy0.02gbpchf-e1.208161.191611.219202020.06.05 15:55:451.2192040022.8
2 - gbpchf-e
4000.62
-49.63
622226095222020.06.02 23:54:09buy0.03nzdchf-e0.613110.601860.620612020.06.05 08:48:350.6206140023.41
5 - nzdchf-e
4024.03
-26.81
632226095232020.06.02 23:54:10buy0.03nzdchf-e0.613110.613410.000002020.06.10 22:28:060.6182640016.3
6 - nzdchf-e
4040.33
-26.21
642226313542020.06.03 00:01:53balanceFinancing0-0.57
 
4039.76
-3.41
652226591172020.06.03 22:47:44buy0.04usdjpy-e108.937108.064109.5192020.06.05 15:31:45109.52140021.22
1 - usdjpy-e
4060.98
20
662226591182020.06.03 22:47:45buy0.04usdjpy-e108.937108.9470.0002020.06.08 16:56:18108.9474000.37
2 - usdjpy-e
4061.35
36.3
672226821092020.06.04 00:01:42balanceFinancing04-1.65
 
4059.7
35.73
682227332392020.06.05 00:01:57balanceFinancing0-0.58
 
4059.12
56.95
692227769002020.06.06 00:01:59balanceFinancing0-0.25
 
4058.87
57.32
702228226272020.06.09 00:01:59balanceFinancing0-0.26
 
4058.61
55.67
712228494232020.06.09 23:35:17buy0.03gbpcad-e1.707491.688411.720162020.06.18 15:02:291.68840400-42.44
1 - gbpcad-e
4016.17
55.09
722228494242020.06.09 23:35:18buy0.03gbpcad-e1.707471.688410.000002020.06.18 15:02:291.68840400-42.41
2 - gbpcad-e
3973.76
54.84
732228703232020.06.10 00:01:35balanceFinancing04-0.29
 
3973.47
54.58
742228952622020.06.10 22:29:42sell0.03usdchf-e0.942550.953120.000002020.06.12 19:56:190.95312400-33.44
3 - usdchf-e
3940.03
12.14
752228952612020.06.10 22:29:42sell0.03usdchf-e0.942550.953120.935772020.06.12 19:56:190.95312400-33.44
4 - usdchf-e
3906.59
-30.27
762228953742020.06.10 22:31:27balanceADJUSTMENT0-68
 
3838.59
-30.56
772229171452020.06.11 00:01:32balanceFinancing0-2.53
 
3836.06
-64
782229634452020.06.12 00:01:37balanceFinancing0-1.38
 
3834.68
-97.44
792230073472020.06.13 00:01:51balanceFinancing0-1.05
 
3833.63
-165.44
802230583522020.06.16 00:01:50balanceFinancing0-1.02
 
3832.61
-167.97
812231049612020.06.17 00:01:55balanceBalance update0-0.99
 
3831.62
-169.35
822231080582020.06.17 00:01:58balanceFinancing0-0.99
 
3830.63
-170.4
832231088102020.06.17 00:44:15balanceBalance update00.99
 
3831.62
-171.42
842231537272020.06.18 00:02:00balanceFinancing0-3.06
 
3828.56
-172.41
85
 
3828.56
-173.4
Splash
Cell Formulas
RangeFormula
O2,O4:O85O2=IF(E2="","",COUNTIF($E$2:E2,E2)&" - "&E2)
P2:P85P2=P1+N2
O3O3=IF(E3="","",COUNTIF($E$2:E119,E3)&" - "&E3)
T4T4=SUM(P1:P252)
T5T5=SUMIF($N$2:$N$252,">0")
T6T6=SUMIF($N$2:$N$252,"<0")*-1
T7T7=AVERAGE(N2:N251)
T8T8=T5/T6
U9U9=COUNTIF(A2:A254, ">0")
U10U10=COUNTIF(N2:N254,">0")
U11U11=COUNTIF(N2:N254,"<0")
U12U12=MAX(N2:N254)
U13U13=MIN(IF(F2:F252<>"",N2:N252,""))
U14U14=AVERAGEIF(N2:N254, ">0")
U15U15=AVERAGEIF(N2:N254, "<0")
U16U16=COUNTIF(C2:C254, "buy")
U17U17=COUNTIF(C2:C254, "sell")
U18U18=COUNTIFS(C2:C254, "buy", N2:N254, ">0")
U19U19=COUNTIFS(C2:C254, "sell", N2:N254, ">0")
T20T20=MAX(FREQUENCY(IF(N2:N254>0,ROW(N2:N254)),IF(N2:N254<=0,ROW(N2:N254))))
T21T21=MAX(FREQUENCY(IF(N2:N255<0,ROW(N2:N255)),IF(N2:N255<=0,ROW(N2:N255))))
T22T22=MAX(Y4:Y254)
T23T23=MIN(Y4:Y254)
T24T24=SUM(K2:K254)
T26T26=U10/U9
V29V29=MIN(IF(F2:F252>0, N2:N254,"0"))
Y4Y4=N2
Y5:Y85Y5=IF(SIGN(5)=SIGN(4),Y4+N2,N2)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
I realise that's a lot of information but it may help hopefully.

There are other alternative as well: for example excluding "Balance" from the calculation in Column D Please ignore the 0 in cloumn F,I added that to try a different formula but got the same result
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
I should add the target Cell is U13, you probably could have figured that out but just in case you got lost in the sea of information.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
How is column F created? It appears there aren't blanks but there are 0s.

Try:
Code:
=MIN(IF(F2:F252<>0,N2:N252,""))
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
Thanks so much for you help! That formula returned -$100 too ( data from N49).

The zeros were added after, I have removed them again, the natural state of the cell is blank. And that's how they are now.

The data is pulled from an HTML sheet which is generated from a trading program.

Is there a way to exclude "balance" from the formula (Column C), though your formulas should work, I'm really not sure why they are returning values that should be excluded??
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
Is the data in F actually blank in some cells or could there be a non-printing character in the cells that look blank?

I just copied all of your data from the above example, removed all the 0s from column F (made the cells blank) and used the original formula and got -42.44 as the min value in column N.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,100,040
Messages
5,472,127
Members
406,805
Latest member
AlesD6

This Week's Hot Topics

Top