COUNTIFS function help please

andrewmurray86

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

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

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
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
27
Office Version
2016
Platform
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,049
Office Version
365, 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
27
Office Version
2016
Platform
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
27
Office Version
2016
Platform
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,100,037
Messages
5,472,112
Members
406,804
Latest member
xbinsx

This Week's Hot Topics

Top