Count Unique Items Per Day from Comma Delimited Cells

[sdickeson]

Date	Reason
1/25/2015	red
1/25/2015	blue, red
1/26/2015	green, blue
1/26/2015	blue
1/26/2015	red
1/27/2015	green, red
1/27/2015	red, green

<tbody>
</tbody>

Hello, I have a table with comma delimited data in the cells. I need to count the number of unique items in the lists per day, as in the table below.

	red	blue	green
1/25/2015	2	1	0
1/26/2015	1	2	1
1/27/2015	2	0	2

<tbody>
</tbody>

This would be easy with a pivot table if the cells weren't comma delimited, but I'm having trouble with it separating the unique items. Is there a simple way to do this, maybe using the countif function? I'm using Excel 2013.

Thanks so much!

 

[VBA Geek]

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">$D2=$A$2:$A$8</font>)*ISNUMBER(<font color="Red">SEARCH(<font color="Green">", "&E$1&", ",", "&$B$2:$B$8&", "</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

Excel 2013
	A	B	C	D	E	F	G
1	Date	Reason			red	blue	green
2	1/25/2015	red		1/25/2015	2	1	0
3	1/25/2015	blue, red		1/26/2015	1	2	1
4	1/26/2015	green, blue		1/27/2015	2	0	2
5	1/26/2015	blue
6	1/26/2015	red
7	1/27/2015	green, red
8	1/27/2015	red, green
Sheet2

 

[AlphaFrog]

If you have excel 2007 or later, you could use the COUNTIFS function (D2 is the date , E1 is the color)

=COUNTIFS($A:$A, $D2, $B:$B, "*"&E$1&"*")

 

[sdickeson]

Thank you for the speedy responses! Those both worth perfectly You saved my afternoon!

 

[GerryZ]

@sdikeson
if you need to make a unique list of dates
here it is...
<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:14pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:161.6px;" /><col style="width:133.6px;" /><col style="width:39.2px;" /><col style="width:98.4px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:solid;border-color:#f0f0f0; ">D</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">25/01/2015</td><td >red</td><td > </td><td style="text-align:right; ">25/01/2015</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">25/01/2015</td><td >blue, red</td><td > </td><td style="text-align:right; ">26/01/2015</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">25/01/2015</td><td >green, blue</td><td > </td><td style="text-align:right; ">27/01/2015</td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">26/01/2015</td><td >blue</td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">27/01/2015</td><td >red</td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">27/01/2015</td><td >green, red</td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="border-width:3; border-style:solid;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">27/01/2015</td><td >red, green</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D1</td><td >{=IFERROR(INDEX<span style=' color:008000; '>($A$1:$A$7,SMALL<span style=' color:#0000ff; '>(IF<span style=' color:#ff0000; '>(FREQUENCY<span style=' color:#804000; '>(IF<span style=' color:#ff7837; '>(A1:A7<>"",$A$1:$A$7)</span>,$A$1:$A$7)</span>,ROW<span style=' color:#804000; '>($B$1:$B$7)</span>-ROW<span style=' color:#804000; '>($B$1)</span>+1)</span>,ROWS<span style=' color:#ff0000; '>($D$1:D1)</span>)</span>)</span>,"")}</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4