# COUNTIFS formula keeps counting blank cell with formulas.

1. ## 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.`

2. ## Re: COUNTIFS formula keeps counting blank cell with formulas.

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:

3. ## Re: COUNTIFS formula keeps counting blank cell with formulas.

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

Where are you using this formula?

4. ## Re: COUNTIFS formula keeps counting blank cell with formulas.

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!))
??

5. ## Re: COUNTIFS formula keeps counting blank cell with formulas.

The formula in question is in G10 on Monthly tab.

6. ## Re: COUNTIFS formula keeps counting blank cell with formulas.

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?

7. ## Re: COUNTIFS formula keeps counting blank cell with formulas.

Well, it took some doing, but I solved it.

Instead of COUNTIFS, I used SUMPRODUCT and replaced this portion of the formula:
Formula:
`Please Login or Register  to view this content.`

with this:
Formula:
`Please Login or Register  to view this content.`

and now, no more counting empty cells with formulas.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1