Would like to get N/A when sumifs = 0, but formula to complicated to know where to include

[laura.gomper]

Hello guys, have just received this formula from a colleague (who is currently on holidays) and would like to get N/A every time is = 0, the problem is that my knowledge is not enough to know where to put the statement IF:

=IF(AND(C$16+($A28-1-1)*T01_D_Freq>T01_D_Cut,C$16+($A28-1)*T01_D_Freq<=T01_D_Last),IFERROR(T01_Yp_FA+T01_Yp_FM*SUMIFS(INDEX(dClean!rClean_Data,,T01_Yc),dClean!rClean_Dt,"<=" & (C$16+($A28-1)*T01_D_Freq),dClean!rClean_Dt,">" & (C$16+($A28-1-1)*T01_D_Freq),INDEX(dClean!rClean_Data,,T01_Yc),"<>#N/A"),NA()),NA())

And i would like to know where exactly to place if sumifs= 0, then N/A. Trying for the last two days and just receive from excel "that i am using too many arguments".

Is anybody able to help? thank you in advance!

[XLent]

taking the requirement literally, i.e. error if SUMIFS returns 0, one option would be to change

*SUMIFS(INDEX(dClean!rClean_Data,,T01_Yc),dClean!rClean_Dt,"<=" & (C$16+($A28-1)*T01_D_Freq),dClean!rClean_Dt,">" & (C$16+($A28-1-1)*T01_D_Freq),INDEX(dClean!rClean_Data,,T01_Yc),"<>#N/A")

to

*1/(1/SUMIFS(INDEX(dClean!rClean_Data,,T01_Yc),dClean!rClean_Dt,"<=" & (C$16+($A28-1)*T01_D_Freq),dClean!rClean_Dt,">" & (C$16+($A28-1-1)*T01_D_Freq),INDEX(dClean!rClean_Data,,T01_Yc),"<>#N/A"))

the above should invoke the "if_error" element of the IFERROR (#N/A) whenever the SUMIFS returns 0

[Limor_OP]

Hi,
Perhaps try this:

=IF(AND(C$16+($A28-1-1)*T01_D_Freq>T01_D_Cut,C$16+($A28-1)*T01_D_Freq<=T01_D_Last)>0,IFERROR(T01_Yp_FA+T01_Yp_FM*SUMIFS(INDEX(dClean!rClean_Data,,T01_Yc),dClean!rClean_Dt,"<=" & (C$16+($A28-1)*T01_D_Freq),dClean!rClean_Dt,">" & (C$16+($A28-1-1)*T01_D_Freq),INDEX(dClean!rClean_Data,,T01_Yc),"<>#N/A"),NA()),NA())

[laura.gomper]

OMG, it works. I could never imagine to get such a fast and accurate reply. Thank you thank you so much XLent. you have improved my week 200%. If i can help you in any way pls don't hesitate to ask! Cheers and have a nice day <3

[laura.gomper]

Thanks also to you Belinda! just seen your reply now