Combining multiple countifs

[datadummy]

I am trying to combine multiple countifs and am not getting my desired amount. What I am trying to do is take multiple sets of criteria (departments) from the same column and then look at an additional column for a "YES" response and then divide all of those counts by a total count of all the specified departments. Here is an example of my current formula,

=COUNTIFS('Raw Data'!E:E,"FBC",'Raw Data'!G:G,"Yes")/COUNTIF('Raw Data'!E:E,"FBC")+COUNTIFS('Raw Data'!E:E,"Maternal Child",'Raw Data'!G:G,"Yes")/(COUNTIF('Raw Data'!E:E,"Maternal Child"))+COUNTIFS('Raw Data'!E:E,"NICU",'Raw Data'!G:G,"Yes")/COUNTIF('Raw Data'!E:E,"NICU")

Currently this is giving me 300% rather than 100%, I'm sure I have made an error somewhere in the process. I am open to another formula if there is a better one.

 

[Eric W]

Maybe:

=SUM(COUNTIFS('Raw Data'!E:E,{"FBC","Maternal Child","NICU"},'Raw Data'!G:G,"Yes"))/SUM(COUNTIF('Raw Data'!E:E,{"FBC","Maternal Child","NICU"}))

 

[datadummy]

That did the trick, thank you!!

I have another dilemma that I'm trying to get to work, I am taking a similar methodology where I am looking at column E for my department, then looking at 2 separate columns (AO,AQ) for a "YES" response in either. Should this be an OR statement and how should I write the function?

 

[Eric W]

Well you could try something like:

=SUMPRODUCT(--(E:E="NICU"),SIGN((AO:AO="yes")+(AQ:AQ="yes")))

where NICU is the department. However, you may notice a bit of a lag with this formula. SUMPRODUCT is not as efficient with whole column references as COUNTIF is. It's best to use the bottom row like this:

=SUMPRODUCT(--(E1:E100="NICU"),SIGN((AO1:AO100="yes")+(AQ1:AQ100="yes")))


Note that the + is the equivalent of an OR.

 

[datadummy]

Here is what I entered and am getting an error...
=SUMPRODUCT('Raw Data'!(E2:E1158="NICU"),SIGN(('Raw Data'!AO2:AO1158="Yes")+('Raw Data'!AQ2:AQ1158="Yes")))

Any thoughts?

 

[Robby19]

Try;

=SUMPRODUCT(--('Raw Data'!E2:E1158="NICU"),SIGN(('Raw Data'!AO2:AO1158="Yes")+('Raw Data'!AQ2:AQ1158="Yes")))

 

[datadummy]

That worked so would there be a way to incorporate the step above where you included all the departments, FBC, Maternal Child, NICU?

 

[Eric W]

OK, getting a little complicated here

=SUMPRODUCT(--ISNUMBER(MATCH('Raw Data'!E2:E1158,{"FBC","Maternal Child","NICU"},0)),SIGN(('Raw Data'!AO2:AO1158="Yes")+('Raw Data'!AQ2:AQ1158="Yes")))

 

[datadummy]

That is seriously impressive, thank you for all your help!