if error with countifs() need to return #N/A

[bloodybrit90]

Hey guys the two formulas below are returning 0 if there is not a value. I have used this formula with median(if()) and average(if()) and it works. Any help will be greatly appreciated.

=IFERROR(COUNT(IF('Both MLS RS'!$BE$33:$BE$10000>='Zipcode Analysis'!$N2,IF('Both MLS RS'!$BE$33:$BE$10000<='Zipcode Analysis'!$O2,IF($A$3='Both MLS RS'!$FH$33:$FH$10000,'Both MLS RS'!$A$33:$A$10000)))),NA())

=IFERROR(COUNTIFS('Both MLS RS'!$BE$33:$BE$10000,">="&$N2,'Both MLS RS'!$BE$33:$BE$10000,"<="&$O2,'Both MLS RS'!$FG$33:$FG$10000,"yes",'Both MLS RS'!$FH$33:$FH$10000,"="&'Zipcode Analysis'!$A$3,'Both MLS RS'!$A$33:$A$10000,">0"),NA())

 

[bloodybrit90]

any Ideas?

 

[bloodybrit90]

I have tried this unsucessfully.

=IF(COUNTIFS("=0",NA(),COUNTIFS('Both MLS RS'!$BE$33:$BE$10000,">="&$C2,'Both MLS RS'!$BE$33:$BE$10000,"<="&$D2,'Both MLS RS'!$FG$33:$FG$10000,"yes",'Both MLS RS'!$FH$33:$FH$10000,"="&'Zipcode Analysis'!$A$2,'Both MLS RS'!$A$33:$A$10000,">0")))

 

[jai9]

Hi,

I am not sure what exactly you are looking for.

I understand that you want to display #NA if COUNTIFS returns 0.

If yes, then try..

=IF(COUNTIFS(.....)=0,"#NA",COUNTIFS(.......))

jai

 

[bloodybrit90]

I need to return #N/A so that a graph will not display the 0's when I have a range highlighted to graph. If you know of a better way please advise.
Cheers

 

[Vidar]

HI

Not sure if this will work, but give it a try.

=IFERROR(IF(COUNT(IF('Both MLS RS'!$BE$33:$BE$10000>='Zipcode Analysis'!$N2,IF('Both MLS RS'!$BE$33:$BE$10000<='Zipcode Analysis'!$O2,IF($A$3='Both MLS RS'!$FH$33:$FH$10000,'Both MLS RS'!$A$33:$A$10000))))=0,NA()),NA())

=IFERROR(IF(COUNTIFS('Both MLS RS'!$BE$33:$BE$10000,">="&$N2,'Both MLS RS'!$BE$33:$BE$10000,"<="&$O2,'Both MLS RS'!$FG$33:$FG$10000,"yes",'Both MLS RS'!$FH$33:$FH$10000,"="&'Zipcode Analysis'!$A$3,'Both MLS RS'!$A$33:$A$10000,">0")=0,NA()),NA())

Vidar

 

[bloodybrit90]

No it returned #N/A when there should be a value or False if used as an array formula.

 

[bloodybrit90]

Thanks for the attempt. Any other ideas?

 

[Vidar]

Could you upload the workbook to Dropbox or some other online storage sites,
so we could take a look at it?

 

[barry houdini]

Try this version to get #N/A instead of zero

=IFERROR(1/(1/COUNTIFS('Both MLS RS'!$BE$33:$BE$10000,">="&$N2,'Both MLS RS'!$BE$33:$BE$10000,"<="&$O2,'Both MLS RS'!$FG$33:$FG$10000,"yes",'Both MLS RS'!$FH$33:$FH$10000,"="&'Zipcode Analysis'!$A$3,'Both MLS RS'!$A$33:$A$10000,">0")),NA())