+ Reply to Thread
Results 1 to 4 of 4

COUNTIF function

  1. #1
    Registered User
    Join Date
    06-08-2007
    Posts
    2

    COUNTIF function

    Hi,

    I have a worksheet which i want to use a countif function (i think!?) where i can look at 2 different columns of data and only count if in these columns a particular word is said. e.g.

    column A has a list of region numbers and column I has an assessment result. I just want to do a count so that if column A has the number 102 in it and Column I has the word PASS in it then count this as 1.

    this is what I am using at the moment, although this just counts the total of both:

    =COUNTIF('Retirement Options - Combined'!A2:A599,"*101*")+COUNTIF('Retirement Options - Combined'!I2:I599,"*PASS*")


    this is probably really easy but am struggling somewhat here.

    many thanks

    Ashley

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Try SUMPRODUCT for multiple criteria, i.e. if you want to find rows where the entire contents of column A are 101 and column I "Pass"

    =SUMPRODUCT(--('Retirement Options - Combined'!A2:A599=101),--('Retirement Options - Combined'!I2:I599=*PASS*))

    [assumes 101 is numeric not text, otherwise put quotes around "101"]

    or if 101 and pass are in amongst other text

    =SUMPRODUCT(--ISNUMBER(SEARCH("101",'Retirement Options - Combined'!A2:A599)),--ISNUMBER(SEARCH("Pass",'Retirement Options - Combined'!I2:I599)))

  3. #3
    Registered User
    Join Date
    06-08-2007
    Posts
    2

    Talking

    Great Stuff, Thats Worked, Many Thanks!

  4. #4
    Registered User
    Join Date
    06-08-2007
    Posts
    9
    Ashley,

    You can also use an Array Formula (also called CSE or CTRL-SHIFT-ENTER Formulas).

    For your example you can use:

    =COUNT(IF('Retirement Options - Combined'!A2:A599=101,IF('Retirement Options - Combined'!I2:I599="pass",'Retirement Options - Combined'!A2:A599)))

    The trick is NOT to click on the Green Check to run the formula (you will get 0), but, with the cursor at the end of the last close paren, press CTRL-SHIFT-ENTER at the same time. When you do that, it will put curly braces {} around the formula, and give you the correct result.

    You might do some research on Array Formulas to get an idea of how they can do some pretty miraculous things...

    Sincerely,
    Marlin Snyder

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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