+ Reply to Thread
Results 1 to 7 of 7

COUNTIFS formula keeps counting blank cell with formulas.

  1. #1
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    195

    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: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Big.Moe; 03-02-2017 at 09:54 PM.

  2. #2
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    195

    Re: COUNTIFS formula keeps counting blank cell with formulas.

    Okay, I found a clue. In the blank cells is this formula:
    Formula: copy to clipboard
    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:
    Attached Files Attached Files
    Last edited by Big.Moe; 03-03-2017 at 01:16 AM.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,201

    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?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,201

    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. #5
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    195

    Re: COUNTIFS formula keeps counting blank cell with formulas.

    The formula in question is in G10 on Monthly tab.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,201

    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. #7
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    195

    Lightbulb 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: copy to clipboard
    Please Login or Register  to view this content.

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

    and now, no more counting empty cells with formulas.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need Help With COUNTIFS Formula That Includes Counting Only Unique Cells
    By Non-Prophet in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2015, 10:40 PM
  2. Replies: 11
    Last Post: 09-15-2014, 02:36 AM
  3. Countifs formula counting the wrong column
    By hedefinesme in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-14-2014, 11:42 AM
  4. Replies: 2
    Last Post: 06-25-2014, 11:17 PM
  5. COUNTIFS not counting all numbers in one cell
    By taichi56 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-16-2013, 05:46 PM
  6. COUNTIFS is counting my blank, text fields
    By Hoosaskin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2011, 01:14 PM
  7. Counting Non Blank Cells that Contain Formulas
    By jimswinder in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2006, 06:40 PM

Bookmarks

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