Calculating Percentages Using Countif Function

[TomJB2018]

Hi,

I have Column G 'Release Date Met' which can be populated either Y or N.

I also have Column J 'Number of Incorrect Files Received' which is free text but will be populated with numbers but may be left blank.

I am looking to calculate what percentage of the total responses in Column G are 'Y' and have done this using the formula: =COUNTIF(G4:G985,"Y")/COUNTA(G4:G985)

However, I don't want this percentage to count any data from column G where the same row in column J has a value other than 0.

Is there a way of amending this COUNTIF formula to exclude such values?

 

[steve the fish]

Hi, See if this does it:

=COUNTIFS(G4:G985,"Y",J4:J985,"0")/COUNTIFS(G4:G985,"<>",J4:J985,"0")

 

[TomJB2018]

Hi, See if this does it:

=COUNTIFS(G4:G985,"Y",J4:J985,"0")/COUNTIFS(G4:G985,"<>",J4:J985,"0")

Hi Steve,

Thanks for your help! Unfortunately I'm getting a Divide by Zero error: [#DIV / 0] when I paste this formula in. Any idea why?

 

[steve the fish]

Its because this equals 0

COUNTIFS(G4:G985,"<>",J4:J985,"0")

which means you have no cells in the range G4:G985 that have a value when the cells in the corresponding row in column J equal 0.

 

[TomJB2018]

Its because this equals 0

COUNTIFS(G4:G985,"<>",J4:J985,"0")

which means you have no cells in the range G4:G985 that have a value when the cells in the corresponding row in column J equal 0.

I've now added zero into all blank cells in column J and that's done the trick. Thanks!