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 {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />



<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Reason</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">red</td><td style="font-weight: bold;;">blue</td><td style="font-weight: bold;;">green</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">1/25/2015</td><td style=";">red</td><td style="text-align: right;;"></td><td style=";">1/25/2015</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1/25/2015</td><td style=";">blue, red</td><td style="text-align: right;;"></td><td style=";">1/26/2015</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">1/26/2015</td><td style=";">green, blue</td><td style="text-align: right;;"></td><td style=";">1/27/2015</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">1/26/2015</td><td style=";">blue</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">1/26/2015</td><td style=";">red</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">1/27/2015</td><td style=";">green, red</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">1/27/2015</td><td style=";">red, green</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />

 

[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