Countif

[Papi]

I have dates in columns A of sheet1 and sheet2. What I am trying to do is to look back at sheet1 and return the number of counts a specific code is found in sheet1 column C which matches the same date i.e =COUNTIFS(sheet1!$A:$A,$A2,sheet1!$C:$C,"SW"). There could be six months or more worth of dates in sheet1 but there will be only one of each date in sheet2 so that we can count how many times a code appears in all of the dates in sheet1 i.e. August 6, 2011 may have six of code "SW" etc.

This works fine except on occassion there can be more than one code entered into column C of sheet1 i.e. SW SG or GR TV etc.

How can I get it to count the indivudual codes jammed in with other codes i.e. an SG where there may also be a code SW (or other codes) in the same cells?

 

[Marcelo Branco]

Maybe

=SUMPRODUCT(--(Sheet1!$A$2:$A$500=A2), --(ISNUMBER(SEARCH("SW",Sheet1!$C$2:$C$500))))

I'm assuming your data in Sheet1 rows 2 till 500
(dont use references to entire columns, like A:A or C:C, because these kind of references in SUMPRODUCT cause a very poor performance)

You can use a cell-reference instead of hard-coding "SW"

HTH

M.

 

[Papi]

Thank you kindly. It works great and I used the cell pointer as you advised.

=SUMPRODUCT(--(sheet1!$A$2:$A$500=$A4), --(ISNUMBER(SEARCH(B$3,sheet1!$C$2:$C$500))))

 

[Papi]

Whether I use the hard-code i.e. "SW" or use a cell reference i.e. B$3 I ran into a slight problem. Some of the codes are single characters i.e. "S" and a higher majority are two characters i.e. "SW" "SE". The single "S" and a few others i.e. "N" vs "NE" count the letter "N" in the dobule letter columns i.e. S will count with "SW" "SE" etc. Is there a way around this issue?

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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;text-align: center;;">Date</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Code</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Date</td><td style="font-weight: bold;text-align: center;;">Code</td><td style="font-weight: bold;text-align: center;;">Count</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SW</td><td style="text-align: center;;"></td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;">SG</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SG SW</td><td style="text-align: center;;"></td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;">S</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SG SW</td><td style="text-align: center;;"></td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;">N</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SG SW</td><td style="text-align: center;;"></td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;">NG</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">S</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">S</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SW N</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">NG NW</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">NW</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SW</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SW</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SG SW</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SG</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SW</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SW</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SW</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">NW</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">N</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">8/5/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">SG S</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet 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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$20=E2</font>),--(<font color="Red">ISNUMBER(<font color="Green">SEARCH(<font color="Purple">" "&F2&" "," "&C$2:C$20&" "</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[T. Valko]

Whether I use the hard-code i.e. "SW" or use a cell reference i.e. B$3 I ran into a slight problem. Some of the codes are single characters i.e. "S" and a higher majority are two characters i.e. "SW" "SE". The single "S" and a few others i.e. "N" vs "NE" count the letter "N" in the dobule letter columns i.e. S will count with "SW" "SE" etc. Is there a way around this issue?

Can you post some sample data so we can see what all these codes look like?

 

[Papi]

It works like a charm. Thanks for the help.

 

[markmzz]

It works like a charm. Thanks for the help.

Papi,

I'm happy that your problem was solved.

This is that important.

Markmzz