Best way to combine Countif and OR functions.

**Matthew_Ky** · 03-29-2014, 05:17 PM

There are 3 columns of data B2:B91 C2:C91 D2:D91. The information is simply yes or no. The 4th column of data (E2:E91) is male or female. Individually the data has been counted: =Countifs(B2:B91,"YES",E2:E91,"Male")
=Countifs(B2:B91,"YES",E2:E91,"Female")
=Countifs(C2:C91,"YES",E2:E91,"Male")
=Countifs(C2:C91,"YES",E2:E91,"Female")
=Countifs(D2:D91,"YES",E2:E91,"Male")
=Countifs(D2:D91,"YES",E2:E91,"Female")

I am trying to combine those three into 1 function. If any of the cells display a yes, then that person needs to be counted. The problem with separately adding these together is that there are 3 categories for each person. If 2 of the columns display yes, the count will treat that as 2 separate individuals. I need the function to only count the person once if "Yes" is displayed in any of the 3 columns.

How should I go about this?

**Ron Coderre** · 03-29-2014, 05:35 PM

Try this regular formula to count rows where Yes occurs in B:D AND E=Male

Formula:

Please Login or Register  to view this content.

Is that something you can work with?

**Matthew_Ky** · 03-29-2014, 05:51 PM

the 20s have to be switched to 91 of course, but that worked perfectly! Thank you so much!

**Ron Coderre** · 03-29-2014, 06:04 PM

Glad you got something you can use!

Be sure to mark this thread as SOLVED (from the Thread Tools menu)

**Tony Valko** · 03-29-2014, 06:16 PM

Here's another one...

Data Range

	B	C	D	E	F	G	H
1	------	------	------	------	------	------	------
2	No	No	No	Male		Male	4
3	Yes	Yes	No	Female		Female	3
4	No	No	No	Female
5	Yes	No	No	Male
6	Yes	Yes	Yes	Male
7	Yes	No	No	Female
8	No	Yes	Yes	Female
9	Yes	No	Yes	Male
10	No	Yes	Yes	Male

This formula entered in H2 and copied down:

=SUMPRODUCT(--(MMULT(--(B$2:D$10="Yes"),{1;1;1})>0),--(E$2:E$10=G2))

**Ron Coderre** · 03-29-2014, 07:39 PM

I'm not a big fan of MMULT (too difficult to explain)...but, I was a bit surprised that this worked:

Formula:

Please Login or Register  to view this content.

**Tony Valko** · 03-29-2014, 08:39 PM

Nice and compact!

The SUMPRODUCT version is about twice as fast to calculate on 100 rows of data.

**Tony Valko** · 03-30-2014, 10:39 AM

Originally Posted by Ron Coderre

I'm not a big fan of MMULT (too difficult to explain)

Too difficult to explain how it works or too difficult to explain why you're not a fan?

If it's the former, here's my attempt to explain how it works from a post many years ago:

http://www.tech-archive.net/Archive/.../msg00758.html

As you can see in the URL the post was originally in the old MS NNTP newsgroups and was "mirrored" in this other forum.

**Ron Coderre** · 03-30-2014, 10:57 AM

We've strayed off topic on this one...Please see my PM to you about this.

Best way to combine Countif and OR functions.

LinkBack

Thread Tools

Rate This Thread

Display

Best way to combine Countif and OR functions.

Re: Best way to combine Countif and OR functions.

Re: Best way to combine Countif and OR functions.

Re: Best way to combine Countif and OR functions.

Re: Best way to combine Countif and OR functions.

Re: Best way to combine Countif and OR functions.

Re: Best way to combine Countif and OR functions.

Re: Best way to combine Countif and OR functions.

Re: Best way to combine Countif and OR functions.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How do I combine these two functions?

How do I combine the COUNTIF and the OR functions into a single command

[SOLVED] Combine 5 Functions into One

Combine sum, offset, and countif functions

How do I combine COUNTIF and AND functions in Excel

Bookmarks

Bookmarks

Posting Permissions