Counting Variables in two column ranges

[IainClover]

I have several columns in a sheet called “Data” that have a number of variables, that is intended to collate Equalities Data for an organisation.

In one column Range F3:F200 I have values for an individual’s age ranges i.e

18-21
22-29
30-39
40-49 etc etc

In another column Range K3:K200 I have values for another characteristic e.g religious faith

Anglican
Catholic
Muslim
Buddhist etc etc

In a separate sheet, I am trying to collate the data in the following way …

Individuals aged 22-29 that are also Catholic
Or
Individuals aged 22-29 that are also Muslim etc etc etc

I have tried doing this with a CountIf statement, but am having no joy, can someone advise me of the correct formula

Thanks in advance
Iain C

 

[Toadstool]

Hi Iain,

COUNTIFS came in with Excel 2013 so it should be available to you

IainClover.xlsx
	F					K
1
2	Age Range					Religion
3	18-21					Anglican
4	22-29					Catholic
5	30-39					Muslim
6	40-49					Buddhist
7	18-21					Anglican
8	22-29					Catholic
9	30-39					Muslim
10	50-65					Buddhist
11	50-65					Anglican
12	22-29					Catholic
13
Sheet1

IainClover.xlsx
	A
1	Result
2	3
Sheet2

Cell Formulas
Range	Formula
A2	A2	=COUNTIFS(Sheet1!$F$3:$F$200,"22-29",Sheet1!$K$3:$K$200,"Catholic")

 

[Peter_SSs]

Assuming headings in row 2, including G2:J2, what about using Excel's built-in Pivot Table feature (find it on the 'Insert' ribbon tab) to provide all the counts and totals at once?

21 11 07.xlsm
	F					K	L	M	N	O	P	Q	R	S
1
2	Age Range					Religion		Count of Age Range	Age Range
3	18-21					Anglican		Religion	18-21	22-29	30-39	40-49	50-65	Grand Total
4	22-29					Catholic		Anglican	2				1	3
5	30-39					Muslim		Buddhist				1	1	2
6	40-49					Buddhist		Catholic		3				3
7	18-21					Anglican		Muslim			2			2
8	22-29					Catholic		Grand Total	2	3	2	1	2	10
9	30-39					Muslim
10	50-65					Buddhist
11	50-65					Anglican
12	22-29					Catholic
13
PT

 

[IainClover]

Thank you, I had tried a similar formula but with "Countif" not "Countifs" .... that made all the difference
Iain C

 

[Toadstool]

Thank you, I had tried a similar formula but with "Countif" not "Countifs" .... that made all the difference
Iain C

You're welcome.
...and I'm glad you have COUNTIFS available in your Excel 2013 so I didn't have to resort to SUMPRODUCT

IainClover.xlsx
	A
1	Result
2	3
Sheet3

Cell Formulas
Range	Formula
A2	A2	=SUMPRODUCT(--(Sheet1!$F$3:$F$200="22-29")*(Sheet1!$K$3:$K$200="Catholic"))