Count Unique Items Per Day from Comma Delimited Cells

sdickeson

New Member
Joined
Jun 19, 2014
Messages
6
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!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
<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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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

New Member
Joined
Jun 19, 2014
Messages
6
Thank you for the speedy responses! Those both worth perfectly :) You saved my afternoon!
 

GerryZ

Well-known Member
Joined
Jul 4, 2014
Messages
1,219
@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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,884
Messages
5,598,651
Members
414,251
Latest member
oExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top