Bring Back a Count of a Number Range Based on Values in Another Cell

[Grimlocc]

Hello,

I am struggling with which formula can help me with this so I am coming to you for some help. I have tried to do a COUNTIFS(AND formula but I don’t even know whether that’s a valid combination… I have thought that it may need an index match but I don’t know how to do those, I can only do simple vlookups unfortunately.

My problem is this: I wish to bring back data from one column based on data from another column into a third column. I am doing this so that I can quickly view the age ranges of staff based in each location.

On one sheet (SHEET 1), I have a large data set relating to my staff. One column holds the location they are based in and another column I have the age of each member of staff.

On another sheet (SHEET 2), I have columns for each location and I wish to be able to indicate an age range on the rows below the location. Below is an example of what SHEET2 looks like:

Column B | Column C | Column D | Column E | Column F
Location 1 Location 2 Location 3 Location 4 Location 5
Row 3 20-30
Row 4 30-40
Row 5 40-50
Row 6 50-60
Row 7 60+

SHEET 1 simply has columns with data in them – please see below how it looks. There are lots of columns in between the LOCATION and AGE on my actual spreadsheet but I don’t need any of them for this:

Column A | Column B
Location | Age

Location 1 30
Location 2 45
Location 3 40
Location 1 25
Location 1 60
Location 2 43
Location 5 34
Location 3 39
Location 4 49
Location 2 51
Location 1 39
Location 5 40

And so on…

So, there are a number of different locations in column A and there are multiple staff working at each location. Is it possible to have a formula in SHEET 2 in cell B3 that will look in Column A on SHEET 1 for all of the instances of Location 1 then when it finds them, looks across to column B and counts the number of people with an age between 20 and 30 and gives me the number of people at that location in that age range? Then, in cell B4 on SHEET 2, do the same thing but for the age range 30-40 and so on?

I hope that I’ve managed to explain myself clearly and thank you in advance for any help you can give me!

 

[AlanY]

this assume age 30 belongs to the 30-40 group etc, adjust to suit

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Location	Age				Location 1	Location 2	Location 3	Location 4	Location 5
2	Location 1	30		20	30	1	0	0	0	0
3	Location 2	45		30	40	2	0	1	0	1
4	Location 3	40		40	50	0	2	1	1	1
5	Location 1	25		50	60	0	1	0	0	0
6	Location 1	60		60	99	1	0	0	0	0
7	Location 2	43
8	Location 5	34
9	Location 3	39
10	Location 4	49
11	Location 2	51
12	Location 1	39
13	Location 5	40
229

Cell Formulas
Range	Formula
F2	=COUNTIFS($A$2:$A$13,F$1,$B$2:$B$13,">="&$D2,$B$2:$B$13,"<"&$E2)

 

[Grimlocc]

Thanks very much AlanY - works a charm!

 

[AlanY]

you're welcome