count/sum of numbers between specific range for 2 different values

[Mehow]

Hi All,

So my question is how to show how many numbers fall into a specific range between 2 different numbers:

KWC 21 days wersja dobra.xls
	B	C	D	E	F
5	Start number	End Number	22-30	31-45	46+
6	24	29	5	0	0
7	33	43	0	10	0
8	48	68	0	0	20
9	43	44	0	1	0
10	27	56	4	15	10
11	27	57	3	15	11
Sheet1

The yellow part of the table i have calculated manually and entered the numbers need to come up with a count/sum formula to calculate how many numbers fall into specific range depending on Start/End Date

So if start number is 27 - end number is 56
That means 27,28,29,...,54,55,56
-> so for the 22-30 range we have - 27,28,29,30 = sum of 4
-> for the 31-45 range we have 31,32,33,...,44,45 = sum of 15
-> for the 46+ range we have 46,47,...,54,55 = sum of 10

What formula to use?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

The yellow part of the table i have calculated manually

Have you done it correctly? For example in row 6 the numbers are this aren'y they?
24, 25, 26, 27, 28, 29
If so it looks like 6 of them fit in the 22-30 range, not 5?

.. and surely cells D10 and D11 should be the same as each other since both sets of numbers start at 27 and go beyond 30?

Could you make use of something like this?

22 07 28.xlsm
	A	B	C	D	E
3			22	31	46
4			30	45	1000
5	Start number	End Number	22-30	31-45	46+
6	24	29	6	0	0
7	33	43	0	11	0
8	48	68	0	0	21
9	43	44	0	2	0
10	27	56	4	15	11
11	27	57	4	15	12
Count in Range

Cell Formulas
Range	Formula
C6:E11	C6	=MAX(MIN($B6,C$4)-MAX($A6,C$3)+1,0)