SUMIF Not Working Inside IF Statement

[mcmuney]

1. I have this formula that works: =SUMIF(H1467:I1467,"<>#N/A",H1467:I1467)

2. Now I'm trying to include the SUMIF formula inside of: =IF(I1467="","",IF(A1467=B1467,H1467,H1467+I1467))

3. Making it: =IF(I1467="","",IF(A1467=B1467,H1467,SUMIF(H1467:I1467,"<>#N/A",H1467:I1467)))

The SUMIF formula works on its own (as shown on #1), but it does not work inside the IF statement (as shown on #3).

H1467 = -177
I1467 = #N/A

#1 result = -177
#2 result = #N/A
#3 result = #N/A

Is there a reason for this? If yes, what's the fix?

Thanks.

 

[SanjayGMusafir]

This part is giving error

I1467=""

Check this and revert -

=IF(NOT(ISNA(I1467)),"",IF(A1467=B1467,H1467,SUMIF(H1467:I1467,"<>#N/A",H1467:I1467)))

 

[Fluff]

What should happen if I1467 is #N/A, do you want to show ""

 

[mcmuney]

This part is giving error

I1467=""

Can you tell me why this is an error? In my scenario I1467 has #N/A, which is not ="".

Also, your formula works only when I1467 is #N/A. When it has a value it does not SUM H1467:I1467.

 

[mcmuney]

What should happen if I1467 is #N/A, do you want to show ""

If I1467 is #N/A, it should =H1467

 

[SanjayGMusafir]

Check this and revert -

=IFS(ISNA(I1467),H1467,I1467="","",A1467=B1467,H1467,TRUE,SUMIF(H1467:I1467,"<>#N/A",H1467:I1467))

 

[Fluff]

Another option

=IF(OR(ISNA(I1467),A1467=B1467),H1467,IF(I1467="","",SUMIF(H1467:I1467,"<>#N/A",H1467:I1467)))