ExcelBanter - complex IF formula returns #VALUE!

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - complex IF formula returns #VALUE! (https://www.excelbanter.com/excel-discussion-misc-queries/88317-complex-if-formula-returns-value.html)

smart.daisy

complex IF formula returns #VALUE!

=IF(AND(SUMIF(Form!I1:I100,"C",Form!N1:N100)-form2!G14<1.1,SUMIF(Form!I1:I100,"C",Form!N1:N100)-form2!G14-1.1),"ok","check")

I have a formula like this. I doubt check every cell and these related two
forms. They have valid data there. Could someone give me a hint where I might
find the problem? Thanks.

Barb Reinhardt

complex IF formula returns #VALUE!

I'd change your formula to

=IF(ABS(SUMIF(Form!I1:I100,"C",Form!N1:N100)-Form2!G14)<1.1,"ok","check")

You might want to check that Form!N1:N100 has values in it.

"smart.daisy" wrote:

=IF(AND(SUMIF(Form!I1:I100,"C",Form!N1:N100)-form2!G14<1.1,SUMIF(Form!I1:I100,"C",Form!N1:N100)-form2!G14-1.1),"ok","check")

I have a formula like this. I doubt check every cell and these related two
forms. They have valid data there. Could someone give me a hint where I might
find the problem? Thanks.

All times are GMT +1. The time now is 11:13 PM.