Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    145

    Default COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    I have 2 columns in spreadsheet as mentioned below:

    Column A Column B

    Closed Master
    Open Slave
    Error Master
    Hold Master
    Reopen Slave

    I need to Count all "Closed", "Open" & "Hold" status from Column A and corresponding "Master" status from Column B so that the output of that formula will be "2" as per the above example.

    Thanks Guys!!!!

  2. #2
    Board Regular GopalaKrishnaJ's Avatar
    Join Date
    Dec 2011
    Location
    New Delhi, India
    Posts
    75

    Default Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    Code:
    =SUMPRODUCT(--(B1:B6="Master")*(A1:A6={"Closed","Open","Hold"}))

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Posts
    145

    Default Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    Hey Gopala!! Thanks for the formula but i need some little alternations

    Actually, this formula should count all "Master" from Column A and status NOT EQUAL to "Closed", "Open" & "Hold" from column B. I tried altering the formula but did'nt work

    This what is what i changed which is not working and returning #NA error

    Code:
     =SUMPRODUCT(--(B1:B6="Master")*(A1:A6<>{"Closed","Open","Hold"}))
    Thanks!!

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,804

    Default Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    Quote Originally Posted by petes View Post
    I have 2 columns in spreadsheet as mentioned below:

    Column A Column B

    Closed Master
    Open Slave
    Error Master
    Hold Master
    Reopen Slave

    I need to Count all "Closed", "Open" & "Hold" status from Column A and corresponding "Master" status from Column B so that the output of that formula will be "2" as per the above example.

    Thanks Guys!!!!
    Try...

    =SUM(COUNTIFS(A2:A6,{"Closed","Open,"Hold"},B2:B6,"Master")
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular GopalaKrishnaJ's Avatar
    Join Date
    Dec 2011
    Location
    New Delhi, India
    Posts
    75

    Default Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    If u need other than the {"Closed","Open","Hold"} then try substracting from the whole sum

    Try this:

    Code:
    =counta(A1:A6)-SUMPRODUCT(--(B1:B6="Master")*(A1:A6<>{"Closed","Open","Hold"}))

  6. #6
    Board Regular
    Join Date
    Sep 2009
    Posts
    145

    Default Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    Hey Goapal!!

    Output of this formula is retuning -2. But actually i am expecting 1
    Because, after excluding "Closed","Open","Hold" status, i will be left with "Error" in Column A and "Master" in column B. Hence, it should display 1

    Any thoughts..??

  7. #7
    Board Regular GopalaKrishnaJ's Avatar
    Join Date
    Dec 2011
    Location
    New Delhi, India
    Posts
    75

    Default Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    Mis read the post

    Try it

    Code:
    =SUMPRODUCT(--(B1:B6="Master")*(A1:A6<>"Open")*(A1:A6<>"Closed")*(A1:A6<>"Hold"))
    Last edited by GopalaKrishnaJ; Jul 26th, 2012 at 03:36 AM.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,804

    Default Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    Quote Originally Posted by petes View Post
    Hey Goapal!!

    Output of this formula is retuning -2. But actually i am expecting 1
    Because, after excluding "Closed","Open","Hold" status, i will be left with "Error" in Column A and "Master" in column B. Hence, it should display 1

    Any thoughts..??
    One of:

    =SUM(COUNTIFS(A2:A6,{"Closed","Open,"Hold"},B2:B6,"Master")

    =SUM(COUNTIFS(A2:A6,"<>"&{"Closed","Open,"Hold"},B2:B6,"Master")
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Sep 2009
    Posts
    145

    Default Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    Thanks a lot Gopala!! this is exacatly what i was looking for...

    @Aladin: your fucntion was also useful...

    Thanks again guys!!

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,804

    Default Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

    Quote Originally Posted by petes View Post

    ...
    @Aladin: your fucntion was also useful...

    Thanks again guys!!
    Not only useful, also faster...
    Assuming too much and qualifying too much are two faces of the same problem.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com