Banding Based on Salary

[Excel_77]

Hi All,

I have a main tab and within it I have column A that list salary ranges as follows:

£1000 - £2000
£2001 - £5000
£5001 - 10,000
£10,000 +

Within "salary tab I have a list of 40 people who each fall into one of the criteria above based on the "salary" filed which is in "F" of the "Salary" tab. What I want to do in column B of my main tab is input a formula that displays how many employees fall into each band with the total = 40, what formula should I input?

Thanks in advance

 

[Scott R]

I'd put 1000 in A1 and 2000 in B1, 2001 in A2 and 5000 in B2, ... and 99999 in B4.
Put in C1 and copy down:
=COUNTIFS(J$1:J$40, ">=" & A1, J$1:J$40, "<=" & B1)

 

[Special-K99]

Put the lower tier values in column A
Put the upper tier values in column B
Put 10001 in A4
Put 999999 in B4

Then in C1
=COUNTIFS(Salary!F1:F1000A6,">="&A1,Salary!F1:F1000,"<="&B1)
and copy down