| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CountIF with dates
got it! Thanks!
"Bob Phillips" wrote: Just add it as another test, making sure that you use the same size range =SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431)) ),--(Sheet1!$K$2:$K$143=1)) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Secret Squirrel" wrote in message ... I have one followup question... Say I want to add another array to this formula. I want it to only count up the variances if column (K) has a 1 in it. How would I add this to the formula you gave me? Or should I say how would you add it to the formula? "Bob Phillips" wrote: Because your original example was Column A Column B Column C (actual) (Promised) (Variance) 11/01/06 11/03/06 -2 which is the 3rd. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Secret Squirrel" wrote in message ... That worked! It should be 2006,11,2 (November 2, 2006) Why were you thinking it should be 2006,1,3? "Bob Phillips" wrote: I put a bracket in the wrong place =SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431)) Shouldn't that be 2006,1,3 with your example data? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Secret Squirrel" wrote in message ... Column (L) is where I have the formula that is calculating the variance between the two dates. Here is that formula in case that could be causing the problem: =IF(J2=I2,NETWORKDAYS(I2,J2)-1,NETWORKDAYS(I2,J2)+1) "Bob Phillips" wrote: To add month and year, and count of variances =SUMPRODUCT(--(Sheet1!$A$1:$A$100)=DATE(2006,11,A1),--(Sheet1!$C$1:$C$1001) ) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Secret Squirrel" wrote in message ... The format I have for the date is 11/01/06. So I would need to query the entire date including the month and year. Also, the formula you wrote doesn't have the countif statement in it. I need to be able to count the number of variances that are greater than 1, not a sum of the variances. Column A Column B Column C (actual) (Promised) (Variance) 11/01/06 11/03/06 -2 So what I need to do is count up the values in column C that are greater than 1 for each day of the month. There will be multiple rows for each day so I need to summarize each day on a separate tab in the worksheet. "Bob Phillips" wrote: =SUMPRODUCT(--(DAY(Sheet1!$A$1:$A$100)=A1)) but what about the month being queried? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Secret Squirrel" wrote in message ... I have a worksheet that I am using to calculate on time delivery based on actual ship dates and promised ship dates. I have the countif function calculating the two dates and giving me my result for each line item. Now what I need to do is summarize it by day of the month. I have another worksheet that I'm using to summarize it. What I want to do is have the summary worksheet look at the ship date column (J) and count up all the variances greater than 2 in column (K). On the summary worksheet I have a column list of every day of that particular month so I want put the total for each day next to the actual day on the summary tab. On the summary tab column (A) has the day of the month, column (B) is where I want it to put the countif result for each day. How would I write this formula up? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help on countif and sumif function with dates and wildcard characters | Excel Discussion (Misc queries) | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
| SUMPRODUCT or COUNTIF? | Excel Worksheet Functions | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
| Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
Hybrid Mode
