COUNTIFS formula keeps counting blank cell with formulas.

I'm trying to get the average of three daily stats: unit price, unit cost, unit profit. The problem is that the data is not all neatly in a column, so I came-up with a formula using SUMIF divided by COUNTIFS that worked beautifully until I entered a negative value for unit profit. The criteria ">0" I was using no longer worked since I had to take in consideration negative values. So, I went with "<>0", then tried "<>""", then I tried using both. Each time, the formula seems to keep counting blank cells because there are formulas inside.

My question is a simple one: How do you test for blank cells if they have a formulas in them?

Don't laugh at it's grotesqueness, but here's my really ugly formula:

Formula:

Please Login or Register  to view this content.

Okay, I found a clue. In the blank cells is this formula:

Formula:

Please Login or Register  to view this content.

If I delete that formula, my grotesquely obese formula for averaging works perfectly.

So the question remains how do I account for the "" that the formula is placing in the cell?
I thought that "" meant that the formula made that cell blank?

I've already tieded: "<>", "<>""", ">"""", "<>0", ">"&""

Here's a reduced version of the file:

"blank" cells with formulas are not really blank, they just present (appear) that way.

Where are you using this formula?

If you are referring to column U...
U9=IF($S9="","",SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!)+SUMIF(#REF!,$S9,#REF!))
??

I know I have worked on this for you before, and I think I recommended/suggested that you change the data sheet layout to having all data in 1 table, with each month below the next. I dont recall what your response to that was?