Getting percentage from columns and raws, divide by 0 error...

Hello Ladies and Gents!

Today People on this forum have already helped me hugely, but I have one more small propblem which I hope you help me to solve:

I have a kind of training matrix. Let's say columns have different topics, and rows have people names.

Each topic consists of three columns - Required (Yes/No) | Date of expiry| Validity (VALID/NOT VALID)

I basically need two formulas and I hope I have something to start with:


- one which will get me percentage of compliance of certain training along all people (vertical range)

=COUNTIFS(D5:D11,"VALID",B5:B11,"Yes")/COUNTIF(B5:B11,"Yes")


-another one - compliance of people by all trainings (horizontal range)

=COUNTIFS(A7:L7,"Yes",A7:L7,"VALID")/COUNTIF(A7:L7,"Yes")

The problem is that when All people in a certain group don't require specific training, the formula returns me "divide by zero error", because non of them has "Yes" in the requirement...

Another problem is that when I try to get percentage horizontally (Formula #2), it returns me 0, although I've tested it on one man with two topics, both of them were required, and both of them valid, but the result I got was 0...

I hope you can help me to solve these problems...

Thanks in advance!