+ Reply to Thread
Results 1 to 10 of 10

Countifs

  1. #1
    Registered User
    Join Date
    11-20-2011
    Location
    Trondheim, Norway
    MS-Off Ver
    Excel 2007
    Posts
    31

    Countifs

    Hi!

    Got a simple question regarding the powerful countifs formula: Can I use the same cell as Criteria in multiple ranges?

    All additional ranges have the same number of rows and columns as the criteria_range1 argument.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Countifs

    The short answer is: Yes!
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Countifs

    Yes you can.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    11-20-2011
    Location
    Trondheim, Norway
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Countifs

    Then I must be doing something wrong. I`ve attached a file with a simple countifs formula in sheet1. What am I doing wrong?
    Attached Files Attached Files

  5. #5
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Countifs

    Your formula seems correct.

    The result is 'zero' since there are no entries where both columns (B&C) have the same entry i.e. 'A'

    What is your expected result?
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Countifs

    OR you mean?

    =COUNTIF(B3:C8,B2)

  7. #7
    Registered User
    Join Date
    11-20-2011
    Location
    Trondheim, Norway
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Countifs

    Hi, and thank`s you for your answers.

    I now see I`ve been inaccurate in describing my problem. See new file with more accurate description.

    What I`m looking for is a formula that 1) looks up range K4:K25 = TRUE 2) looks up range C4:J24 = user data entry M4. I then would like to count and sum the intersection 1) and 2)
    Attached Files Attached Files

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Countifs

    Try

    =COUNTIFS(K4:K25,TRUE,I4:I25,M4)

    =SUMIFS(J4:J25,K4:K25,TRUE,I4:I25,M4)

  9. #9
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Countifs

    Tr this..

    Counts
    Please Login or Register  to view this content.
    Sum
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-20-2011
    Location
    Trondheim, Norway
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Countifs

    Thanks Ace_XL, it works perfectly

+ 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