count unique comma separated values from a single cell in excel

[azad092]

Age	1Y-10Y	11Y-20Y	21Y-30Y	31Y-40Y	41Y-50Y	51Y-60Y	Above 60Y
1,5,14,20,30,45,41	0	0	0	0	0	0	0

i want to calculate different age stages using comma separated value formula but it not works properly
how i can do this...
1,5 should be count in 1Y-10Y , 20 should be count in 11Y-20,30 should be count in 21Y-30Y and 45,41 should be count in 41Y-45Y

 

[J.Ty.]

Hi,

Welcome to MrExcel.
I am afraid that you problem, as stated, is very difficult to solve. The principal idea of Excel is that you put information in cells and then process it using formulas.
The principal idea which makes in work correctly is that you put one piece information per cell. It is sometimes called First Normal Form (1 NF).

You design violates the 1 NF principle, because

• The Age field contains many numbers in one cell.
• The fileds in the header contain both the lower and upper age limit, which is two pieces of information.

Therefore I strongly advise you to represent your data in a different form, with one number per cell, and then there are muttiple methods to do waht you want.

J.Ty.

 

[azad092]

Hi,

Welcome to MrExcel.
I am afraid that you problem, as stated, is very difficult to solve. The principal idea of Excel is that you put information in cells and then process it using formulas.
The principal idea which makes in work correctly is that you put one piece information per cell. It is sometimes called First Normal Form (1 NF).

You design violates the 1 NF principle, because

• The Age field contains many numbers in one cell.
• The fileds in the header contain both the lower and upper age limit, which is two pieces of information.

Therefore I strongly advise you to represent your data in a different form, with one number per cell, and then there are muttiple methods to do waht you want.

J.Ty.

thanks for reply
now guide me can we use this formula LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1 with countifs function if possible plz gude how....

 

[J.Ty.]

I am afraid that you data representation is so bad, that you probably cannot do this using formulas. VBA solution is possible, but still the change of data layout is the right way.

J.Ty.

 

[DanteAmor]

Hi azad092, welcome to the board and happy new year:

How about this array formula (Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.)

Book1
	A	B	C	D	E	F	G	H
1	Age	1Y-10Y	11Y-20Y	21Y-30Y	31Y-40Y	41Y-45Y	46Y-50Y	51Y-55Y
2	1,5,14,20,30,45,41	2	2	1	0	2	0	0
3	1,2,3,4,15,22,23,24,25,26,27	4	1	6	0	0	0	0
Hoja2

Cell Formulas
Range	Formula
B2:H3	B2	{=SUM(IF(ISNUMBER(SEARCH(","&ROW(INDIRECT(LEFT(B$1,SEARCH("Y",B$1)-1)+0&":"&MID(B$1,SEARCH("-",B$1)+1,2)+0))&",",","&$A2&",")),1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[DanteAmor]

You can reduce the formula if you put the age ranges as follows, without the "Y", we know that it refers to "Year", then it can be omitted.

Book1
	A	B	C	D	E	F	G	H
1	Age	01-10	11-20	21-30	31-40	41-50	51-60	60-99
2	1,5,14,20,30,45,41	2	2	1	0	2	0	0
3	1,2,3,4,89,15,22,23,99,24,25,26,27	4	1	6	0	0	0	2
Sheet2

Cell Formulas
Range	Formula
B2:H3	B2	{=SUM(--ISNUMBER(SEARCH(","&ROW(INDIRECT(SUBSTITUTE(B$1,"-",":")))&",",","&$A2&",")))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

 

[azad092]

You can reduce the formula if you put the age ranges as follows, without the "Y", we know that it refers to "Year", then it can be omitted.

Book1
	A	B	C	D	E	F	G	H
1	Age	01-10	11-20	21-30	31-40	41-50	51-60	60-99
2	1,5,14,20,30,45,41	2	2	1	0	2	0	0
3	1,2,3,4,89,15,22,23,99,24,25,26,27	4	1	6	0	0	0	2
Sheet2

Cell Formulas
Range	Formula
B2:H3	B2	{=SUM(--ISNUMBER(SEARCH(","&ROW(INDIRECT(SUBSTITUTE(B$1,"-",":")))&",",","&$A2&",")))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

thanks for your guideline ,but i still facing the problem. by using your suggested formula i can only get 1 in result. can you mail me the excel file in which you have apply this formula and also tell in which office you have apply this formula because i am using ms office2007

 

[DanteAmor]

You can copy the example and paste in your excel sheet:

Again I remind you that it is an array formula.
You must put this in the cell B2

=SUM(--ISNUMBER(SEARCH(","&ROW(INDIRECT(SUBSTITUTE(B$1,"-",":")))&",",","&$A2&",")))

Edit the formula and press the Control+Shift+Enter keys

 

[DanteAmor]

I attach my sample file so you can see how the formula works

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[azad092]

I attach my sample file so you can see how the formula works

Dropbox - File Deleted - Simplify your life

www.dropbox.com

your work is so great i impressed but what the magic in your file because the formula works in your file but i copy formula from your file and apply in the same file i does not work it looks like a text plz see the file image