Excel percentage help

[this_is_gav]

Hey guys,

This is a first for me - I'm trying to concoct an Excel formula which will present a percentage based on two matching values.

      A            B             C                D
    Name        Gender     Working Below    Working Above
-------------------------------------------------------------
1  Bob             M             X
2  Fred            M             X
3  Will            M                              X
4  Bill            M                              X
5  Jill            F             X
6  Jen             F             X                   
7  Jess            F                              X
8  Sally           F                              X
-------------------------------------------------------------
9  Boys                         50%              50%
10 Girls                        50%              50%

I've simplified this somewhat, but what I want to get is what percentage of boys are 'working below' and how many are working above.

COUNTIFS(B1:B8,"M") returns the correct number (4 in this case) and COUNTIFS(C1:C8,"X") returns 4 too, but combine them into =COUNTIFS(B1:B8,"M")/COUNTIFS(C1:C8,"X") and the value returned is way off (it should be 50% for both boys and girls).

Am I doing something spectacularly stupid here or am I just going the wrong way?

 

[feriso]

Doing it the wrong way, if you are getting 1 then that is right as counting M get 4, counting X gets 4; so 4/4 =1.

What you are looking for (I assume) is the number of M with X in Col C (which is 2 hence 50%) so you need to set the countifs to multiple criteria of Col b AND Col C, hence:-

=(COUNTIFS(B4:B11,"M", C4:C11,"X"))/COUNTIFS(B4:B11,"M")

 

[this_is_gav]

You sir are a genious.

It was actually =(COUNTIFS(B4:B11,"M", C4:C11,"X"))/COUNTIFS(C4:C11,"X") that resulted in the correct amount (otherwise it was 25%), but it seems to be working.

My head was starting to hurt. I've come up with formulae more complex than that, but I just couldn't get my head around this one for some reason. Thanks again!