+ Reply to Thread
Results 1 to 5 of 5

COUNTA, SUMPRODUCT, COUNTIF, Counting blank cells.

  1. #1
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    500

    COUNTA, SUMPRODUCT, COUNTIF, Counting blank cells.

    Morning Excel Helpers,

    I have a requirement where I need to count a bunch of codes less another bunch of codes in a range.

    I am trying to use this formula

    Please Login or Register  to view this content.
    K56:X56 is the range to count, and the OneCodes is the 'named range' of codes NOT to count, The Formula does count the codes but unfortunately it counts the blank cells too, I've tied adding a COUNTIF(K56:X56<>0,) into the mix but without success.

    Does anyone have any ideas ?

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: COUNTA, SUMPRODUCT, COUNTIF, Counting blank cells.

    Try using this formula to count cells that are both non-blank and not equal to codes in OneCodes

    =SUMPRODUCT((COUNTIF(OneCodes,$K56:$X56)=0)*($K56:$X56<>""))
    Audere est facere

  3. #3
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    500

    Re: COUNTA, SUMPRODUCT, COUNTIF, Counting blank cells.

    Just tried Mr Longlegs it works in the fact that it doesn't count the blank cells, but how can I adjust that to also not count 'O' zero's ?

    I forgot to mention this in the original question :-(
    Last edited by BlindAlley; 12-09-2017 at 11:11 AM.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: COUNTA, SUMPRODUCT, COUNTIF, Counting blank cells.

    You can add additional conditions, e.g.

    =SUMPRODUCT((COUNTIF(OneCodes,$K56:$X56)=0)*($K56:$X56<>"")*($K56:$X56<>0))

  5. #5
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    500

    Re: COUNTA, SUMPRODUCT, COUNTIF, Counting blank cells.

    Thanks, that works very well

+ 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. [SOLVED] how can i get the result of counta function without counting the blank cells
    By sumesh56 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-03-2014, 03:20 PM
  2. Counta function counting blank cells
    By FieldHaven in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-23-2013, 08:36 PM
  3. COUNTA counting blank cells?
    By David_1952 in forum Excel General
    Replies: 2
    Last Post: 06-16-2010, 12:35 PM
  4. Sumproduct OR adding criteria to Countif, Counta and Average
    By Wkruger in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-24-2010, 03:32 AM
  5. Sumproduct counting blank cells
    By raghavfastest in forum Excel General
    Replies: 3
    Last Post: 01-09-2009, 05:39 AM
  6. Errors in COUNT, COUNTA, COUNTIF when counting merged cells
    By Outback in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-07-2006, 12:35 PM
  7. sumproduct--counting--zero--blank cells
    By jeremy via OfficeKB.com in forum Excel General
    Replies: 4
    Last Post: 08-16-2005, 11:05 AM

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