# COUNTIFS function help please

#### andrewmurray86

##### New Member
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
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

### 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
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
OK, I got it, I entered as an array Ctrl+Shift+Enter and it worked

Thank you so much.

#### kweaver

##### Well-known Member
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
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
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")

Replies
4
Views
123
Replies
16
Views
218
Replies
4
Views
352
Replies
33
Views
710
Replies
2
Views
146

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

### 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