Fixing a formula

[datadummy]

I am seeking assistance in fixing a formula that is resulting in either #Div/O or #VALUE , here is my current formula,

=COUNTIFS('Raw May'!E1:E1200,"General Medicine",'Raw May'!AU1:AU1200,"Yes")/COUNTIFS('Raw May'!E1:E1200,"General Medicine",'Raw May'!AU1:AU1200,"Yes",'Raw May'!AU1:AU1200,"No",'Raw May'!AU1:AU1200,"Nurse Clarified Concerns")

The column AU does have blanks in the column and am not sure if that is causing the issue or not.

 

[ardykav]

Its because you have conflicting rules in the later part of the formula

"COUNTIFS('Raw May'!E1:E1200,"General Medicine",'Raw May'!AU1:AU1200,"Yes",'Raw May'!AU1:AU1200,"No",'Raw May'!AU1:AU1200,"Nurse Clarified Concerns")"

Are you saying that you want to count if Column E has General Medicine and Column AU has Yes,no or Nurse Clarified in it? The way you have the formula written means it needs to satisty all those things at once which is impossible.

You need to either have multiple countifs added together or use SUM like below

=SUM(COUNTIFS(AU:AU,{"Yes";"No";"Nurse Clarified"},E:E,"General Medicine"))

 

[Aladin Akyurek]

Perhaps...

=COUNTIFS('Raw May'!E1:E1200,"General Medicine",'Raw May'!AU1:AU1200,"Yes")/COUNTIFS('Raw May'!E1:E1200,"General Medicine",'Raw May'!AU1:AU1200,"?*")

if AU cannot but have Yes, No, Nurse Clarified Converns, and blanks tha must be excluded from the ratio you are looking for.

Otherwise, the more specific (see also the ardykav post)…

=COUNTIFS('Raw May'!E1:E1200,"General Medicine",'Raw May'!AU1:AU1200,"Yes")/SUM(COUNTIFS('Raw May'!E1:E1200,"General Medicine",'Raw May'!AU1:AU1200,{"Yes","No","Nurse Clarified Concerns"}))

 

[datadummy]

Ok that worked, but now I am noticing that the data in column AU has multiple entries so my thought is I would like to count any entry in column AU but omit any blanks any thoughts on how to achieve that? Would I simply put ""?

 

[Aladin Akyurek]

Ok that worked, but now I am noticing that the data in column AU has multiple entries so my thought is I would like to count any entry in column AU but omit any blanks any thoughts on how to achieve that? Would I simply put ""?

The denominator of

=COUNTIFS('Raw May'!E1:E1200,"General Medicine",'Raw May'!AU1:AU1200,"Yes")/COUNTIFS('Raw May'!E1:E1200,"General Medicine",'Raw May'!AU1:AU1200,"?*")

does just that.

Counting all of the text values of AU...

=COUNTIFS('Raw May'!AU1:AU1200,"?*")​

​