COUNTIFS Function for Multiple Criteria in Single & Multiple Column

Thanks:  0
Likes:  0

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

1. ## 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. ## Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

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

3. ## 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. ## Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

Originally Posted by petes
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")

5. ## 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. ## 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. ## 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"))`

8. ## Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

Originally Posted by petes
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")

9. ## Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

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

Thanks again guys!!

10. ## Re: COUNTIFS Function for Multiple Criteria in Single & Multiple Column

Originally Posted by petes

...

Thanks again guys!!
Not only useful, also faster...

## User Tag List

#### Posting Permissions

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