Adjusting a formula

[datadummy]

I currently am utilizing the formula below to get percent of compliance where the entries in column E were either "Y" or "N", but now need to tweak my formula as some of the entries in column E are now "NA". What I would like to accomplish is taking those responses of "NA" and removing them from the equation.

=IFERROR(SUM(COUNTIFS(Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!C:C,"AB",Data!E:E,{"Y"}))/COUNTIFS(Data!C:C,"AB",Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020"),"NA")

 

[datadummy]

you want to count the number of Y divide by the number of Y & N

I think the error is div by zero error - is that what the
COUNTIFS(Data!C:C,"Maternal Child",Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020")
returns ?
a zero with na entered

sorry , this is taking a while to resolve difficult without the spreadsheet or posted using XL2BB
or a link to dropbox/onedrive

=IFERROR(SUM(COUNTIFS(Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!C:C,"Maternal Child",Data!E:E,{"Y"}))/SUM(COUNTIFS(Data!C:C,"Maternal Child",Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",data!EE, {"Y","N"}),"NA")
this is just dividing by the count of the Y and N

you want to count the number of Y divide by the number of Y & N

I think the error is div by zero error - is that what the
COUNTIFS(Data!C:C,"Maternal Child",Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020")
returns ?
a zero with na entered

sorry , this is taking a while to resolve difficult without the spreadsheet or posted using XL2BB
or a link to dropbox/onedrive

=IFERROR(SUM(COUNTIFS(Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!C:C,"Maternal Child",Data!E:E,{"Y"}))/SUM(COUNTIFS(Data!C:C,"Maternal Child",Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",data!EE, {"Y","N"}),"NA")
this is just dividing by the count of the Y and N

I updated my formula and now excel is saying that I am missing an opening or closing parenthesis and I have no clue where. Here is the formula I entered.

=IFERROR(SUM(COUNTIFS(Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!C:C,"Maternal Child",Data!E:E,{"Y"}))/SUM(COUNTIFS(Data!C:C,"Maternal Child",Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!E:E,{"Y","N"}),"NA")

 

[etaf]

i need to see a sample of the data , not really sure what is going on

i just tried to setup a spreadsheet , BUT not really sure what is going on as i only get a 0 or NA with the formula
BUT it looks like you are trying to work out a percent of Y in column E based on the total Y and N in column E, based on a date range and AB in another column
But I dont seem to get that

 

[datadummy]

I have attached an image of my test entry. Here is the current formula that is stating that I am missing an opening or closing parenthesis.

=IFERROR(SUM(COUNTIFS(Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!C:C,"Maternal Child",Data!E:E,{"Y"}))/SUM(COUNTIFS(Data!C:C,"Maternal Child",Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!E:E,{"Y","N"}),"NA")

 

[etaf]

OK so this works ignoring NA in E and gives correct % 0.5 ignoring NA and 0.3 with NA

= COUNTIFS(Data!A:A,">25/10/2020",Data!A:A,"<31/10/2020",Data!C:C, "AB",Data!E:E, "Y")/SUM(COUNTIFS(Data!A:A,">25/10/2020",Data!A:A,"<31/10/2020",Data!C:C, "AB",Data!E:E,{"Y","N"}))

Book2
	G	H	I	J
1	COUNT Y	COUNT E	% E NA	% E Y/N
2	1	3	0.33333333	0.5
Sheet1

Cell Formulas
Range	Formula
G2	G2	= COUNTIFS(Data!A:A,">25/10/2020",Data!A:A,"<31/10/2020",Data!C:C, "AB",Data!E:E, "Y")
H2	H2	= COUNTIFS(Data!A:A,">25/10/2020",Data!A:A,"<31/10/2020",Data!C:C, "AB")
I2	I2	= COUNTIFS(Data!A:A,">25/10/2020",Data!A:A,"<31/10/2020",Data!C:C, "AB",Data!E:E, "Y")/COUNTIFS(Data!A:A,">25/10/2020",Data!A:A,"<31/10/2020",Data!C:C, "AB")
J2	J2	= COUNTIFS(Data!A:A,">25/10/2020",Data!A:A,"<31/10/2020",Data!C:C, "AB",Data!E:E, "Y")/SUM(COUNTIFS(Data!A:A,">25/10/2020",Data!A:A,"<31/10/2020",Data!C:C, "AB",Data!E:E,{"Y","N"}))

DATA SHEET

Book2
	A	B	C	D	E
2
3	10/26/20		AB		Y
4					Y
5	10/27/20		AB		N
6	10/28/20		ab		NA
Data

 

[etaf]

need another ) after the {"Y","N"}

=IFERROR(SUM(COUNTIFS(Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!C:C,"Maternal Child",Data!E:E,{"Y"}))/SUM(COUNTIFS(Data!C:C,"Maternal Child",Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!E:E,{"Y","N"})),"NA")

 

[datadummy]

need another ) after the {"Y","N"}

=IFERROR(SUM(COUNTIFS(Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!C:C,"Maternal Child",Data!E:E,{"Y"}))/SUM(COUNTIFS(Data!C:C,"Maternal Child",Data!A:A,">10/25/2020",Data!A:A,"<10/31/2020",Data!E:E,{"Y","N"})),"NA")

SUCCESS!!! Thank you for dredging through this with me, I truly appreciate your patience!!