How Do I Intentionally return #N/A in SUMIFS and Other Formulas?

[Steve N.]

Good evening folks! Hope everyone is well. I'm looking for a way to get a couple of different formulas to return #N/A where there is no data to be plotted for the upcoming months. As this is my first post, I hope I'm not breaching protocal by leaving the details in the attachment provided.

Any assistance, advice and awesome excel wizardry would be well appreciated.

Steve

[vlady]

Use N/A()

something like

=if(sumifs=0,N/A(),sumifs)
underlined - test the value of sumifs if 0 then put #N/A

=IF(T2=0,NA(),T5+S7)

[Steve N.]

I tried the sumifs suggestion like this =if(SUMIFS=0, NA(),SUMIFS(Div_800_WOs_In,Div_800_Month,T$1,Div_800_Year,$J$1) and got #NAME? Something with my sintax?

I do have the =IF(T2=0,NA(),T5+S7) in there and it works fine if T2 is >1 but if only T3 is >1 the other formulas don't return the proper value... Suggestions?

[vlady]

My bad!!

i didn't notice i put / in NA()

Please Login or Register  to view this content.

in second query

maybe put an (and)
=IF(and(T2=0,t3=0),NA(),T5+S7)

[Steve N.]

The SUMIFS works perfectly! Thank you.

I may have oversimplified the other problem. If either T2 or T3 are greater than 0 the formulas in T4:T7 must return a value.

[vlady]

Then change and with or

This is the formula in T4

=IF(OR(T2>0,T3>0),T3/T2,NA())

[Steve N.]

Still no go. I'm going to try some 'and/or' combos and see where I get.

[Steve N.]

Thanks for helping me with the SUMIFs problem. Your solution will save me loads of time!