COUNTIFS function help please

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. 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.
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
I got a Value error on the Clean formula beyond the cells with values, I impleneted this if error to give zero
=IFERROR((CLEAN(N3))*1,0)
But still got the -100 figure. Clearly this spreadsheet is being super difficult
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
I have other formulas I need help with but they also need to exclude the same values so it may not be helpful to get them working if the sheet can't exclude the values??
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
OK, I got it, I entered as an array Ctrl+Shift+Enter and it worked

Thank you so much.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,583
Office Version
  1. 365
  2. 2010
CLEAN doesn't handle two other CHARs. You might try:

Code:
 =SUBSTITUTE(N2,CHAR(127),””)

and

Code:
  =TRIM(SUBSTITUTE(N2,CHAR(160),” “))

and see if that makes any difference. CHAR(160) is a non-breaking space and is used often on the web.
Make sure you get the straight quotes in that formula.
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
I'm now trying to get the average loss value correct with this formula
=AVERAGEIF(N2:N252,(IF(C2:C252<>"balance",(N2:N252<0))))
Essentially to exclude the same data as before but I'm getting a Div/0 error whether I enter as a formula or array?
 

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
If I use this formula I get an average with the values that should be excluded, but aren't.
=AVERAGEIFS(N2:N252,(IF(C2:C252<>"balance",N2:N252,"")),"<0")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,595
Messages
5,625,710
Members
416,128
Latest member
WarJamAnd

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
Top