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

COUNTIFS Function for Multiple Criteria in Single & Multiple Column

This is a discussion on COUNTIFS Function for Multiple Criteria in Single & Multiple Column within the Excel Questions forums, part of the Question Forums category; I have 2 columns in spreadsheet as mentioned below: Column A Column B Closed Master Open Slave Error Master Hold ...

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

    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
    143

    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
    65,221

    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
    143

    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
    65,221

    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
    143

    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
    65,221

    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.

Page 1 of 2 12 LastLast

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