COUNTIFS function help please

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
Actually blank, there is no data in those cells
Interesintly I tried
=MIN(IF(F2:F25<>"",N2:N25,""))
limited range

And got a #value error
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
When I used the formula for rows 2 to 25, I got a min value of -40.64.
All this is suggesting to me is that there are extraneous characters in the F and/or the N column that came over from your download.

See what you get with:

Code:
=MIN(IF(TRIM(F2:F252)<>"",N2:N252,""))
Alternatively, as you suggested, this also gives -42.44

Code:
=MIN(IF(C2:C252<>"balance",N2:N252,""))
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
I'm still getting -100 in the target cell for the "balance" formula

and #Value! for the Trim formula

:(
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
Well, as you can see, that's not what I get in U13

Book7
ABCDEFGHIJKLMNOPQRSTU
1
2213919162020.05.06 00:29:47sell0.03eurnzd-e1.791991.8143102020.05.06 00:31:351.79421400-4.031 - eurnzd-e3995.97
32.21E+082020.05.06 00:29:47sell0.03eurnzd-e1.791991.814311.773632020.05.06 00:31:341.79421400-4.046 - eurnzd-e3991.93Updated when trade is closed
42.21E+082020.05.06 00:38:04sell0.03eurnzd-e1.792231.8145402020.05.06 00:38:311.79424400-3.653 - eurnzd-e3988.28Realized Profit/Loss770536.2Profit/Loss of all closed trades
52.21E+082020.05.06 00:38:04sell0.03eurnzd-e1.792231.814541.773862020.05.06 00:38:301.79424400-3.644 - eurnzd-e3984.64Gross profit617.83Total profit of all won trades
62.21E+082020.05.06 00:38:46sell0.03gbpjpy-e132.515133.84702020.05.06 00:39:37132.666400-4.251 - gbpjpy-e3980.39Gross loss789.27Total loss of all lost trades
72.21E+082020.05.06 00:38:46sell0.03gbpjpy-e132.515133.847131.4792020.05.06 00:39:36132.666400-4.262 - gbpjpy-e3976.13Expected payoff-2.06554Average trade outcome
82.22E+082020.05.11 23:13:38buy0.05audjpy-e69.86169.00770.7152020.05.13 19:18:0969.007400-39.921 - audjpy-e3936.21Profit factor0.782787Gross profit / gross loss
92.22E+082020.05.11 23:13:39buy0.05audjpy-e69.86169.00702020.05.13 19:18:0969.007400-39.912 - audjpy-e3896.3Number of closed tradesPer Year83
102.22E+082020.05.12 00:01:07balanceFinancing-0.163896.14Profit trades31
112.22E+082020.05.13 00:01:35balanceFinancing-0.163895.98Loss trades52
122.22E+082020.05.14 23:32:09sell0.07nzdcad-e0.841510.8495302020.05.19 23:50:010.84721400-28.771 - nzdcad-e3867.21Largest profit trade94.11
132.22E+082020.05.14 23:32:09sell0.07nzdcad-e0.841510.849530.834132020.05.19 23:50:000.84722400-28.822 - nzdcad-e3838.39Largest loss trade
-42.44
142.22E+082020.05.15 23:31:25sell0.03gbpjpy-e129.765131.203128.3912020.05.18 18:23:07131.203400-40.393 - gbpjpy-e3798Average profit trade19.93
152.22E+082020.05.15 23:31:26sell0.03gbpjpy-e129.765131.20302020.05.18 18:23:07131.203400-40.394 - gbpjpy-e3757.61Average loss trade-15.18
162.22E+082020.05.15 23:40:17sell0.05nzdchf-e0.576280.584110.569152020.05.18 16:41:360.58412400-40.641 - nzdchf-e3716.97Long trades30
172.22E+082020.05.15 23:40:18sell0.05nzdchf-e0.576280.5841102020.05.18 16:41:360.58412400-40.642 - nzdchf-e3676.33Short trades22
182.22E+082020.05.16 00:01:53balanceFinancing-0.923675.41Long trades won24
192.22E+082020.05.19 00:01:31balanceFinancing-0.323675.09Short trades won6
Sheet2
Cell Formulas
RangeFormula
U13U13=MIN(IF(C2:C252<>"balance",N2:N252,""))
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
Shall I perhaps send the workbook? I'm really confused as to how this is happening.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
You can't upload a workbook on the site because of concerns about a possible virus.

You might try something like this in the AA2 cell and fill down:

Code:
=SUBSTITUTE(N2,CHAR(CODE(LEFT(N2))),"")
and see if there's a 202 (leading character) somewhere.
 
Last edited:

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
That's understandable.
I'll upload the section again
Copy of Andrew_Murray_Trade_Results_2020.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
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
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
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
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
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
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
102216292502020.05.12 00:01:07balanceFinancing-0.16
 
3896.14
Profit trades
31
112218096392020.05.13 00:01:35balanceFinancing-0.16
 
3895.98
Loss trades
52
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
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
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
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
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
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
182220471072020.05.16 00:01:53balanceFinancing-0.92
 
3675.41
Long trades won
24
192221012802020.05.19 00:01:31balanceFinancing-0.32
 
3675.09
Short trades won
6
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)
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)
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)
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)
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
252221284942020.05.19 23:49:09buy0.03eurusd-e1.092501.092600.000002020.05.22 08:04:431.092604000.3
2 - eurusd-e
3812.78
262221503252020.05.20 00:01:34balanceFinancing4-0.48
 
3812.3
Win Rate
37.35%
#VALUE!
272221764432020.05.20 22:47:13buy0.03audusd-e0.660110.685940.000002020.06.09 12:22:560.6914840094.11
1 - audusd-e
3906.41
282221764422020.05.20 22:47:13buy0.03audusd-e0.660110.647460.668552020.06.01 03:18:310.6685640025.35
2 - audusd-e
3931.76
292221764552020.05.20 22:49:17sell0.02usdcad-e1.389481.408241.377222020.05.26 20:25:011.3772240017.72
1 - usdcad-e
3949.48
-100
302221764562020.05.20 22:49:18sell0.02usdcad-e1.389481.356960.000002020.06.09 12:22:581.3480940061.11
2 - usdcad-e
4010.59
312221985372020.05.21 00:01:48balanceFinancing4-2.68
 
4007.91
322222235572020.05.21 23:39:48buy0.03audchf-e0.637260.637360.000002020.05.27 17:47:110.637354000.28
1 - audchf-e
4008.19
332222235562020.05.21 23:39:48buy0.03audchf-e0.637260.626170.644652020.05.27 10:47:140.6446640022.78
2 - audchf-e
4030.97
-100
342222235792020.05.21 23:44:22buy0.03nzdchf-e0.594300.594400.000002020.05.29 18:01:400.594394000.28
3 - nzdchf-e
4031.25
352222235772020.05.21 23:44:22buy0.03nzdchf-e0.594300.583370.601592020.05.26 18:00:450.6016040022.52
4 - nzdchf-e
4053.77
362222375342020.05.22 00:01:27balanceFinancing-0.46
 
4053.31
372222769442020.05.23 00:01:31balanceFinancing-0.24
 
4053.07
382223258612020.05.26 00:01:34balanceFinancing-0.37
 
4052.7
392223760162020.05.27 00:01:31balanceFinancing-0.25
 
4052.45
402224020702020.05.27 22:50:09buy0.04cadchf-e0.703510.695210.000002020.05.29 17:57:320.69518400-34.86
1 - cadchf-e
4017.59
412224020692020.05.27 22:50:09buy0.04cadchf-e0.703510.695210.709052020.05.29 17:57:320.69518400-34.87
2 - cadchf-e
3982.72
422224250192020.05.28 00:01:51balanceFinancing-0.63
 
3982.09
432224519502020.05.28 23:30:54buy0.02gbpusd-e1.231951.215050.000002020.06.01 05:53:321.240410016.92
1 - gbpusd-e
3999.01
442224519492020.05.28 23:30:54buy0.02gbpusd-e1.231951.215051.243222020.06.01 05:53:321.2404140016.92
2 - gbpusd-e
4015.93
452224519572020.05.28 23:32:39sell0.04usdchf-e0.964140.973330.958332020.06.01 05:53:210.9601040016.84
1 - usdchf-e
4032.77
462224519582020.05.28 23:32:40sell0.04usdchf-e0.964140.973330.000002020.06.01 05:53:220.9601040016.83
2 - usdchf-e
4049.6
472224739622020.05.29 00:02:04balanceFinancing-0.92
 
4048.68
482225254872020.05.30 00:01:40balanceFinancing-0.37
 
4048.31
492225392172020.06.01 09:11:07balanceADJUSTMENT-100
 
3948.31
502225392272020.06.01 09:11:43balanceADJUSTMENT-20
 
3928.31
512225786202020.06.02 00:01:32balanceFinancing-0.17
 
3928.14
522226095022020.06.02 23:48:51buy0.04audcad-e0.932240.932540.000002020.06.03 11:08:350.932524000.82
1 - audcad-e
3928.96
532226095012020.06.02 23:48:51buy0.04audcad-e0.932240.920990.939742020.06.03 04:07:130.9397640022.2
2 - audcad-e
3951.16
542226095052020.06.02 23:48:59buy0.03audchf-e0.663850.664150.000002020.06.09 10:18:150.664134000.88
3 - audchf-e
3952.04
552226095042020.06.02 23:48:59buy0.03audchf-e0.663850.651790.671892020.06.05 15:58:120.6719040024.93
4 - audchf-e
3976.97
562226095132020.06.02 23:50:46sell0.02euraud-e1.619271.644930.000002020.06.04 23:39:091.63130400-16.8
1 - euraud-e
3960.17
572226095122020.06.02 23:50:46sell0.02euraud-e1.619271.644931.602732020.06.04 23:39:081.63138400-16.91
2 - euraud-e
3943.26
582226095152020.06.02 23:51:16sell0.02eurnzd-e1.753681.745070.000002020.06.10 01:29:001.7450740011.14
5 - eurnzd-e
3954.4
592226095142020.06.02 23:51:16sell0.02eurnzd-e1.753681.781261.736062020.06.05 15:32:141.7360640022.82
6 - eurnzd-e
3977.22
602226095192020.06.02 23:52:08buy0.02gbpchf-e1.208161.208460.000002020.06.09 11:26:131.208454000.6
1 - gbpchf-e
3977.82
612226095182020.06.02 23:52:08buy0.02gbpchf-e1.208161.191611.219202020.06.05 15:55:451.2192040022.8
2 - gbpchf-e
4000.62
622226095222020.06.02 23:54:09buy0.03nzdchf-e0.613110.601860.620612020.06.05 08:48:350.6206140023.41
5 - nzdchf-e
4024.03
632226095232020.06.02 23:54:10buy0.03nzdchf-e0.613110.613410.000002020.06.10 22:28:060.6182640016.3
6 - nzdchf-e
4040.33
642226313542020.06.03 00:01:53balanceFinancing-0.57
 
4039.76
652226591172020.06.03 22:47:44buy0.04usdjpy-e108.937108.064109.5192020.06.05 15:31:45109.52140021.22
1 - usdjpy-e
4060.98
662226591182020.06.03 22:47:45buy0.04usdjpy-e108.937108.9470.0002020.06.08 16:56:18108.9474000.37
2 - usdjpy-e
4061.35
672226821092020.06.04 00:01:42balanceFinancing4-1.65
 
4059.7
682227332392020.06.05 00:01:57balanceFinancing-0.58
 
4059.12
692227769002020.06.06 00:01:59balanceFinancing-0.25
 
4058.87
702228226272020.06.09 00:01:59balanceFinancing-0.26
 
4058.61
712228494232020.06.09 23:35:17buy0.03gbpcad-e1.707491.688411.720162020.06.18 15:02:291.68840400-42.44
1 - gbpcad-e
4016.17
722228494242020.06.09 23:35:18buy0.03gbpcad-e1.707471.688410.000002020.06.18 15:02:291.68840400-42.41
2 - gbpcad-e
3973.76
732228703232020.06.10 00:01:35balanceFinancing4-0.29
 
3973.47
742228952622020.06.10 22:29:42sell0.03usdchf-e0.942550.953120.000002020.06.12 19:56:190.95312400-33.44
3 - usdchf-e
3940.03
752228952612020.06.10 22:29:42sell0.03usdchf-e0.942550.953120.935772020.06.12 19:56:190.95312400-33.44
4 - usdchf-e
3906.59
762228953742020.06.10 22:31:27balanceADJUSTMENT-68
 
3838.59
772229171452020.06.11 00:01:32balanceFinancing-2.53
 
3836.06
782229634452020.06.12 00:01:37balanceFinancing-1.38
 
3834.68
792230073472020.06.13 00:01:51balanceFinancing-1.05
 
3833.63
802230583522020.06.16 00:01:50balanceFinancing-1.02
 
3832.61
812231049612020.06.17 00:01:55balanceBalance update-0.99
 
3831.62
822231080582020.06.17 00:01:58balanceFinancing-0.99
 
3830.63
832231088102020.06.17 00:44:15balanceBalance update0.99
 
3831.62
842231537272020.06.18 00:02:00balanceFinancing-3.06
 
3828.56
85
 
3828.56
86
 
3828.56
87
 
3828.56
88
 
3828.56
89
 
3828.56
90
 
3828.56
91
 
3828.56
92
 
3828.56
93
 
3828.56
94
 
3828.56
95
 
3828.56
96
 
3828.56
97
 
3828.56
98
 
3828.56
99
 
3828.56
100
 
3828.56
101
 
3828.56
102
 
3828.56
103
 
3828.56
104
 
3828.56
105
 
3828.56
106
 
3828.56
107
 
3828.56
Splash
Cell Formulas
RangeFormula
O2,O4:O107O2=IF(E2="","",COUNTIF($E$2:E2,E2)&" - "&E2)
P2:P107P2=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(C2:C252<>"balance",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
V26V26=MIN(IF(TRIM(F2:F252)<>"",N2:N252,""))
V29V29=MIN(IF(F2:F200<>"",N2:N200,""))
U33U33=MIN(IF(C2:C200<>"balance",N2:N200,""))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
I'm afraid that doesn't help because when that data is copied, only the raw data is copied.
After I did that (again), I wrote the formula in U25 (there are alternate blank lines in the copied data; it's U13 if the blank lines are removed) and still got -42.44
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
Hmmm OK. Well Thanks for your help, I truly appreciate your time and knowledge here.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
Here's another idea. In, say, the Z column (Where there's nothing relating to your data), enter this:

Code:
=(CLEAN(N2))*1
and fill that down.

Then, change the formula in U13 to:

Code:
=MIN(IF(C2:C252<>"balance",Z2:Z252,""))
And see if that gives you the min value now.

The CLEAN function gets rid of a lot of issues (not all).
 

Watch MrExcel Video

Forum statistics

Threads
1,100,033
Messages
5,472,105
Members
406,802
Latest member
Jakub3

This Week's Hot Topics

Top