count number of occurrences based on condition

[mark9988]

Hello,

I'm trying to create a formula that will count the number of occurrences for a specific value.

For example, column A and column B are my inputs in the table below. Column C is my output.

In column C, I would need the formula to count how many times a city is listed under their respective state.

California for instance has 3 cities listed and they are San Diego, Los Angeles and Irvine. Therefore, the value 3 would be listed in column C for California.

A city may be listed more than once, so duplicates would need to be excluded. For example, San Diego is listed twice in column A, but it would only count as one.

Thank you for your time!
Mark9988



<b>Excel 2010</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 /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;background-color: #B8CCE4;;">State</td><td style="font-weight: bold;text-align: center;background-color: #B8CCE4;;">City</td><td style="font-weight: bold;text-align: center;background-color: #B8CCE4;;">Cities found in State</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">CA</td><td style=";">San Diego</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">CA</td><td style=";">San Diego</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">CT</td><td style=";">Hartford</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">CT</td><td style=";">Hartford</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">FL</td><td style=";">Miami</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">CA</td><td style=";">Los Angeles</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">CA</td><td style=";">Irvine</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">TX</td><td style=";">Dallas</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">TX</td><td style=";">Austin</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">CT</td><td style=";">New Haven</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">WA</td><td style=";">Seattle</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">AZ</td><td style=";">Phoenix</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">AZ</td><td style=";">Phoenix</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">NC</td><td style=";">Charlotte</td><td style="text-align: center;;">1</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 />

 

[machopicho]

Bump

 

[bebo021999]

Name:
state = A3:A16
city = B3:B16

=SUM(IF(state=A3,1)*IF(MATCH(city,city,0)=ROW(INDIRECT("1:"&ROWS(city))),1))

Finish with Ctrl-Shift-Enter
Fill down.

 

[mark9988]

bebo021999...wow, this formula is great! It's probably a simple task for you to create something like this, but I really didn't think there was a solution to this problem.

thank you so much for your time and effort!

 

[bebo021999]

bebo021999...wow, this formula is great! It's probably a simple task for you to create something like this, but I really didn't think there was a solution to this problem.

thank you so much for your time and effort!

You are welcome! nice to serve.

 

[T. Valko]

Hello,

I'm trying to create a formula that will count the number of occurrences for a specific value.

For example, column A and column B are my inputs in the table below. Column C is my output.

In column C, I would need the formula to count how many times a city is listed under their respective state.

California for instance has 3 cities listed and they are San Diego, Los Angeles and Irvine. Therefore, the value 3 would be listed in column C for California.

A city may be listed more than once, so duplicates would need to be excluded. For example, San Diego is listed twice in column A, but it would only count as one.

Thank you for your time!
Mark9988



Excel 2010<TABLE style="BORDER-RIGHT: #bbb 1px solid; BORDER-TOP: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BORDER-BOTTOM: #bbb 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #b8cce4; TEXT-ALIGN: center">State</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #b8cce4; TEXT-ALIGN: center">City</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #b8cce4; TEXT-ALIGN: center">Cities found in State</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>CA</TD><TD>San Diego</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>CA</TD><TD>San Diego</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>CT</TD><TD>Hartford</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>CT</TD><TD>Hartford</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>FL</TD><TD>Miami</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD>CA</TD><TD>Los Angeles</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD>CA</TD><TD>Irvine</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD>TX</TD><TD>Dallas</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD>TX</TD><TD>Austin</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD>CT</TD><TD>New Haven</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD>WA</TD><TD>Seattle</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD>AZ</TD><TD>Phoenix</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD>AZ</TD><TD>Phoenix</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">16</TD><TD>NC</TD><TD>Charlotte</TD><TD style="TEXT-ALIGN: center">1</TD></TR></TBODY></TABLE>

Sheet2​

Try this array formula** entered in C3 and copied down:

=SUM(IF(FREQUENCY(IF(A$3:A$16=A3,MATCH(B$3:B$16,B$3:B$16,0)),ROW(B$3:B$16)-ROW(B$3)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the data range of column B.