Help with CountIF functions in Excel 2003

[Mumov1]

Please can anyone help!

I am trying to enter a countif function in order to calculate the number of yes & no's to male & females!

In one column I have 'Y & N' & in a 2nd column 'M & F'. I need to be able to calulate the number of Male Yes & No' & the number of female Yes & No.

Hope this make sense.

I have been using this function of add the number of females & males with this function:

=COUNTIF(K4:K100,"*"&P20&"*")

I don't know if this is possible or If I need to alter the way I collate this data.

Any advice will be gratefully appreciated.

Helen

 

[MaxSmart]

Hi,

Are you looking for something along these lines;

Excel 2012
	A	B	C	D
1			MY	5
2			MN	1
3			FY	2
4			FN	4
5	Gender	Response
6	M	Y
7	M	N
8	F	Y
9	F	N
10	M	Y
11	F	N
12	M	Y
13	F	N
14	F	Y
15	F	N
16	M	Y
17	M	Y
18
Sheet1

Cell Formulas
Range	Formula
D1	=COUNTIFS(A:A,"M",B:B,"Y")
D2	=COUNTIFS(A:A,"M",B:B,"N")
D3	=COUNTIFS(A:A,"F",B:B,"Y")
D4	=COUNTIFS(A:A,"F",B:B,"N")

 

[Noodleski]

Hi Max,

I don't think sumifs will work, as the OP works in a Excel 2003 environment, where sumifs isn't present yet.
Use sumproduct instead. (formula below for the female yes count. I'm sure you can work out the other cases.)

sumproduct(--(A1:A100="F"),--(B1:B100="Y"))

sumproduct formulae require to use the same length ranges and can't handle full column references. (don't substitute A1:A100 for A:A)

 

[MaxSmart]

Sorry,

Did not realise that Excel 2003 did not support COUNTIFS, so have a another go with something like this;

Excel 2012
	A	B	C	D	E
1			Excel	2012	2003
2			MY	5	5
3			MN	1	1
4			FY	2	2
5			FN	4	4
6	Gender	Response
7	M	Y
8	M	N
9	F	Y
10	F	N
11	M	Y
12	F	N
13	M	Y
14	F	N
15	F	Y
16	F	N
17	M	Y
18	M	Y
19
Sheet1

Cell Formulas
Range	Formula
D2	=COUNTIFS(A:A,"M",B:B,"Y")
D3	=COUNTIFS(A:A,"M",B:B,"N")
D4	=COUNTIFS(A:A,"F",B:B,"Y")
D5	=COUNTIFS(A:A,"F",B:B,"N")
E2	=SUMPRODUCT(--(A$7:A$18="M"),--(B$7:B$18="Y"))
E3	=SUMPRODUCT(--(A$7:A$18="M"),--(B$7:B$18="N"))
E4	=SUMPRODUCT(--(A$7:A$18="F"),--(B$7:B$18="Y"))
E5	=SUMPRODUCT(--(A$7:A$18="F"),--(B$7:B$18="N"))

 

[Mumov1]

Hi

Sorry for not replying sooner, only had a chance to try this out today.

I have tried what you suggested but just returns 0. I am beginning to wonder if I should just conceed to the face that we run a very outdated system in work & it can't be done in 2003.

I have attached an example of what I am trying to achieve.


Failed image removed by moderator

 

[Peter_SSs]

I have attached an example of what I am trying to achieve.

Clearly, that didn't work. My signature block below has a link for more information about posting small screen shots, & where to test before trying in an actual thread.