Countif Help

[Johnstog]

I have kind of a complex equation I could use assistance with.

In column B:B I have a date "1/01/2011"
In Column E:E I have various units "BI201", "BI205", etc.
In column H:H i have and item code "P", "IFT", "L" etc.

What I need help with is this:
I need to know for all dates in January 2011
How many times did "BI201" match up to the Code "P", "IFT", "L"

Any help you can provide would be greatly appreciated. Thanks ahead of time for any help you can provide.

 

[Haseeb Avarakkan]

Hi,

Enter Sun,Mon,Tue...Sat in F4:F10
F2, First Criteria
G2, Second Criteria
<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></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;">2</td><td style="text-align: right;;">1/31/2011</td><td style=";">BI208</td><td style=";">P</td><td style="text-align: right;;"></td><td style=";">BI201</td><td style=";">P</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3/11/2011</td><td style=";">BI210</td><td style=";">P</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;">4</td><td style="text-align: right;;">1/4/2011</td><td style=";">BI201</td><td style=";">P</td><td style="text-align: right;;"></td><td style=";">Sun</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1/19/2011</td><td style=";">BI203</td><td style=";">L</td><td style="text-align: right;;"></td><td style=";">Mon</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">3/29/2011</td><td style=";">BI207</td><td style=";">L</td><td style="text-align: right;;"></td><td style=";">Tue</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2/1/2011</td><td style=";">BI204</td><td style=";">IFT</td><td style="text-align: right;;"></td><td style=";">Wed</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">1/21/2011</td><td style=";">BI201</td><td style=";">P</td><td style="text-align: right;;"></td><td style=";">Thu</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1/18/2011</td><td style=";">BI201</td><td style=";">XX</td><td style="text-align: right;;"></td><td style=";">Fri</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">3/13/2011</td><td style=";">BI208</td><td style=";">P</td><td style="text-align: right;;"></td><td style=";">Sat</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1/26/2011</td><td style=";">BI201</td><td style=";">P</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;">12</td><td style="text-align: right;;">4/20/2011</td><td style=";">BI203</td><td style=";">L</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;">13</td><td style="text-align: right;;">1/15/2011</td><td style=";">BI201</td><td style=";">P</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;">14</td><td style="text-align: right;;">2/5/2011</td><td style=";">BI201</td><td style=";">L</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;">15</td><td style="text-align: right;;">12/23/2010</td><td style=";">BI207</td><td style=";">IFT</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;">16</td><td style="text-align: right;;">4/9/2011</td><td style=";">BI200</td><td style=";">P</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;">17</td><td style="text-align: right;;">2/18/2011</td><td style=";">BI203</td><td style=";">IFT</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;">18</td><td style="text-align: right;;">2/2/2011</td><td style=";">BI210</td><td style=";">L</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;">19</td><td style="text-align: right;;">1/17/2011</td><td style=";">BI208</td><td style=";">L</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;">20</td><td style="text-align: right;;">2/18/2011</td><td style=";">BI209</td><td style=";">XX</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</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">G4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">TEXT(<font color="Green">$B$2:$B$20,"ddd"</font>)=F4</font>),--(<font color="Red">$C$2:$C$20=F$2</font>),--(<font color="Red">$D$2:$D$20=G$2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down.

If you want to use Sunday,Monday....Saturday

replace "ddd" with "dddd"

 

[Johnstog]

The WEEKDAY Function works, but it still holds a value of a full day, so My SUMPRODUCT formula still gives me a "0" Value.

Any other ideas

 

[Johnstog]

I got it. I reformatted the days of the week to a text format and it work.

 

[bolo]

The WEEKDAY Function works, but it still holds a value of a full day, so My SUMPRODUCT formula still gives me a "0" Value.

Any other ideas

Not sure what you mean, the weekday returns a value between 1 and 7 (or between 0 and 6 if the second parameter is 3), and not the value of the full day. In any event Haseeb's formula should work fine.

 

[T. Valko]

=SUMPRODUCT(--(B2:B20-DAY(B2:B20)+1=J2),--(E2:E20=K2),--(H2:H20=L2))

This worked well. Thanks.

I need to calculate by day of week as well. So, if =SUMPRODUCT(--(B2:B20-DAY(B2:B20)+1=J2),....... Is what you use to get the Month & Year, what would I use for Day of Week?

I need to know how many fall on each day of the week.

Ok, then enter the day of the week to count for in cell J2. For example, Monday. Make sure you enter the weekday to count for in the same format. Do this:

Monday
Tuesday
Wednesday
Thursday
etc

Don't do this:

Mon
Tuesday
Wed
Thursday

Then, the formula becomes:

=SUMPRODUCT(--(TEXT(B2:B20,"dddd")=J2),--(E2:E20=K2),--(H2:H20=L2))