Help with #VALUE! error please

Sorry for all the mess. I did even cut a whole bunch out to try to simplify it but there's a lot of connections to figure out it's kinda difficult.

But it's simple, just the tops of columns C, D, and E. The three counters. They're compiling data for past 30/60/90 days.

C works, but D and E don't.

You'll notice they rely basically on the same formula as C just different columns.

I'm not so good at figuring out by tracing the calculations what's wrong with a formula but I think Excel said it had to do with the column in the table under C, yet the working formula under C also uses this column and didn't run into this #VALUE! error, so what's going on? Thanks

Your formulas are right, the reason it is not working is because you have #VALUE errors in your net profit column and then you have non dates in your date listed2 column

To test, I simply removed all blanks, #VALUE errors, and other items that were not either a currency in D or a date in E and then the formulas worked.

sample.jpg

Originally Posted by NewYears1978

Your formulas are right, the reason it is not working is because you have #VALUE errors in your net profit column and then you have non dates in your date listed2 column

To test, I simply removed all blanks, #VALUE errors, and other items that were not either a currency in D or a date in E and then the formulas worked.

Attachment 822239

Okay, thanks so much! So how do I get it to ignore these errors?

Like, is there an if error then skip?

but why isn't column C affected by the errors under "net profits" even though it too uses it?

Originally Posted by juntjoo

but why isn't column C affected by the errors under "net profits" even though it too uses it?

Hm, that's a good question which has got me confused. Usually a SUMIF will always error (in my experience) if the data type isn't correct or there are errors. I can't seem to figure out why C works but not D and E..

Also, an IFERROR would not work here because since the whole thing is erroring it would just return the error value you chose. I don't think there is a way for the SUMIF to ignore error or improper cells..but I could be wrong.

Originally Posted by NewYears1978

Hm, that's a good question which has got me confused. Usually a SUMIF will always error (in my experience) if the data type isn't correct or there are errors. I can't seem to figure out why C works but not D and E..

Any way you can imagine to ignore the #VALUE! 's? If not not the end of the world, I'll just have to make sure I have a valid value in there

Change your formula in C net profits to this - should work I think.

=IFERROR(IF(ISNUMBER([@[gross profit b4 labor & taxes]]),[@[gross profit b4 labor & taxes]],[@[1(st comp)]]-[@total]-[@5]),"")

hey, thanks. But could you re-translate within the spreadsheet again? I had drastically simplified it. And maybe this way it will be easier for me to decipher what you did. Especially since you totally changed the function. This is getting deep!

LOL, never mind. I JUST figured out on my own what you did AFTER reading what you replied but misunderstanding it. So I'm good. Best solution. And good thing I came to it on my own, even though I had to ignore you and take credit for it first lol. But I'm still curious why column A formulas didn't have any issues if you have any idea. Cuz I'm just always looking for the shortest code