Ignore cells with 0 in COUNTIF formula

[laurenwydo]

I already have the formula, I just need it to exclude cells with a 0.

=COUNTIFS(Cash!Q:Q, "2", Cash!F:F, "1")/COUNTIFS(Cash!F:F, "1")

Basically, in the Q:Q range, there's either a 1, 2 or 0. At the moment there is one "2" and eight "1" and I want the output of the formula to provide me 11% (1/9), but its counting all the cells with 0, which there are 9, and providing 6% (1/18).

Any help would be greatly appreciated!

 

[shift-del]

Please show the data for columns F and Q.

 

[laurenwydo]

 

[shift-del]

I am getting neither 1/9 nor 1/18 but 1/22.

 

[laurenwydo]

I am so sorry, I just had to add data to the sheet and totally spaced that I did.

The number should now be 1/11 vs what it is calculating 1/22. Basically, make the formula ignore all the 0's.

 

[shift-del]

There are no 0 in column F.
Do you mean counting all 1 in column F where there is no 0 in column Q?
Then you should have added this condition to the second COUNTIFS().

 

[laurenwydo]

I don't want the formula to count the 0's in column Q:Q.

 

[Tetra201]

Try

=COUNTIFS(Cash!Q:Q,2,Cash!F:F,1)/COUNTIFS(Cash!Q:Q,"<>0",Cash!F:F,1)

 

[laurenwydo]

=COUNTIFS(Cash!Q:Q,2,Cash!F:F,1)/COUNTIFS(Cash!Q:Q,"<>0",Cash!F:F,1)

Thank you SO much, that worked! I appreciate all the help!

 

[shift-del]

That's what I suggested in #6.