+ Reply to Thread
Results 1 to 2 of 2

Countif Where 'Criteria' is an Array

  1. #1
    Registered User
    Join Date
    02-18-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    9

    Countif Where 'Criteria' is an Array

    I stumbled into this and just don't understand what is going on. I am not a sophisticated Excel person, BTW.

    Assume that the cells A1:A10 contain various names (text). What exactly does the following function do.

    COUNTIF($A$1:$A$10,$A$1:$A$10)

    What i have observed is as follows.

    If I put that formula above into cell B5 (or C5 or D5 ...), it seems to act the same as the formula Countif($A$1:$A$10,A5). And if I put COUNTIF($A$1:$A$10,$A$1:$A$10) into cell D9 the formula seems to act like Countif($A$1:$A$10,A9). And if I were to put COUNTIF($A$1:$A$10,$A$1:$A$10) into cell C14 (where 14 is outside the range of the input arrays) then the result seems to always be zero. All that I have read implies that 'Criteria' is a scalar of some kind, so maybe this is an accident of implementation.

    Is COUNTIF architected to work with two array inputs? And if so how is the second array treated and is the result returned always a scalar value?

    Thanks.

    dave

    ps. I ran into this when I discovered that the formula SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10)) will return the # of unique values at or above the row in which the formula is placed. There are all kinds of things that I don't understand here, starting with why use SUMPRODUCT, on something that (I thought) was a scalar value.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Countif Where 'Criteria' is an Array

    Welcome to the Forum, unfortunately:

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.

+ 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. Return dates that match a single criteria (a countif criteria)
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-07-2014, 12:43 PM
  2. [SOLVED] Count of rows in an array that meet criteria in column 2 and different criteria in column3
    By reynoldslarry in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2013, 01:51 AM
  3. Replies: 0
    Last Post: 10-13-2012, 10:13 PM
  4. [SOLVED] How do I use countif an array for >=45<=50
    By البيانات in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-04-2006, 04:25 PM
  5. An array and countif based on criteria in each column
    By Rochelle B in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-25-2005, 01:05 AM
  6. Countif w/ Multiple Criteria-How do I use countif
    By Patrick_KC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2005, 05:05 PM
  7. [SOLVED] Countif using format criteria not number criteria?
    By Rumbla76 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-20-2005, 01:06 AM
  8. [SOLVED] Countif using format criteria....not number criteria?
    By Troy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-20-2005, 12:06 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