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

 

[azad092]

w

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 imageView attachment 3059

i observed that we can not edit the formula to change the cell reference according to our need

 

[DanteAmor]

Please Note:

Create an array formula that calculates a single result

This type of array formula can simplify a worksheet model by replacing several different formulas with a single array formula.

1. Click the cell in which you want to enter the array formula.
2. Enter the formula that you want to use.
   Array formulas use standard formula syntax. They all begin with an equal sign (=), and you can use any of the built-in Excel functions in your array formulas.
   For example, this formula calculates the total value of an array of stock prices and shares, and places the result in the cell next to "Total Value."
   
   
   
   The formula first multiplies the shares (cells B2 – F2) by their prices (cells B3 – F3), and then adds those results to create a grand total of 35,525. This is an example of a single-cell array formula because the formula lives in just one cell.
3. Press Ctrl+Shift+Enter.
   When you press Ctrl+Shift+Enter, Excel automatically inserts the formula between { } (a pair of opening and closing braces).

Refrence:

Create an array formula - Microsoft Support

Create array formulas, often called Ctrl Shift Enter or CSE formulas, to perform calculations that generate single or multiple results.

support.office.com

You have to put the following formula and then press the 3 keys at the same time: Control + Shift + Enter

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

 

[azad092]

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

View attachment 3057

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

thanks a lot....your work is so great thanks again

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.

 

[azad092]

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 imageView attachment 3059

hi
dear i am still facing a little issue in this formula, when i add more then one same age values the formula count only one out of them so plz guide me about this issue... thanks

 

[azad092]

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

Dropbox - File Deleted - Simplify your life

www.dropbox.com

hi
i am still facing an issue in age formula when add two same ages in a cell the formula count only one value as you can see in the image so please guide me how i can solve this issue

 

[Tom.Jones]

hi
i am still facing an issue in age formula when add two same ages in a cell the formula count only one value as you can see in the image so please guide me how i can solve this issue

The formula offered by Dante is what you asked for, meaning "count unique comma separated values from a single cell in excel"
Count unique value.

 

[azad092]

The formula offered by Dante is what you asked for, meaning "count unique comma separated values from a single cell in excel"
Count unique v

The formula offered by Dante is what you asked for, meaning "count unique comma separated values from a single cell in excel"
Count unique value.

please see the image and try to understand what i wants to say exactly.. in simple words by this formula i can count different ages from a single cell according to their age range but when i enter two same ages like as 11,11 or 22,22 the formula take only one value form same ages instead of all values... if you understand please guide me thanks

 

[Peter_SSs]

Could you structure your age ranges at the top like this (make sure they are numbers, not numbers stored as text) and then use this standard-entry formula, copied across and down? This also avoids the use of the volatile function INDIRECT.

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

Cell Formulas
Range	Formula
B3:H6	B3	=COUNT(1/(LOOKUP(0+MID(SUBSTITUTE($A3,",",REPT(" ",100)),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($A3)))*100-99,100),$B$1:$H$1)=B$1))

 

[Peter_SSs]

Could you structure your age ranges at the top like this

If you really want/need to keep the ranges as they were before, you can still do it but the formula becomes somewhat longer.

Book1
	A	B	C	D	E	F	G	H
1	Age	1-10	11-20	21-30	31-40	41-50	51-60	61-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
4	11,11,41,52,79,90	0	2	0	0	1	1	2
5	22,22,40	0	0	2	1	0	0	0
Sheet5

Cell Formulas
Range	Formula
B2:H5	B2	=COUNT(1/(LOOKUP(0+MID(SUBSTITUTE($A2,",",REPT(" ",100)),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($A2)))*100-99,100),LEFT($B$1:$H$1,FIND("-",$B$1:$H$1)-1)+0)=LEFT(B$1,FIND("-",B$1)-1)+0))