Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A week of dates and counting them using the COUNTIF function with contingencies
=COUNTIF(Contact_Date,"="&DATE(2007,3,18)-
COUNTIF(Contact_Date,"<="&DATE(2007,3,24))) The above is the formula I am attempting to use. Definitions: Contact_Date is a list of dates that range over 2000 cells that could span up to 1 year. In the example above I am wanting to find any dates that range between and include the dates: 3.18.2007 and 3.24.2007 Problem: The formula I am using is wanting to look past the dates provided and count any prior dates that do not fall within the specified range. I do not know why or how to fix it. Any ideas? In case the formula is seen as html here it is spelled out: =COUNTIF(Contact_Date,"Greater than="&DATE(2007,3,18)- COUNTIF(Contact_Date,"Less than="&DATE(2007,3,24))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A week of dates and counting them using the COUNTIF function with contingencies
Try this:
=COUNTIF(Contact_Date,"="&DATE(2007,3,18)- COUNTIF(Contact_Date,""&DATE(2007,3,24))) The first COUNTIf will count everything with a date later than 17th March, and the second COUNTIF will count everything later than 25th March. You were counting if earlier than 25th March. Hope this helps. Pete On Mar 21, 7:33 pm, "j razz" wrote: =COUNTIF(Contact_Date,"="&DATE(2007,3,18)- COUNTIF(Contact_Date,"<="&DATE(2007,3,24))) The above is the formula I am attempting to use. Definitions: Contact_Date is a list of dates that range over 2000 cells that could span up to 1 year. In the example above I am wanting to find any dates that range between and include the dates: 3.18.2007 and 3.24.2007 Problem: The formula I am using is wanting to look past the dates provided and count any prior dates that do not fall within the specified range. I do not know why or how to fix it. Any ideas? In case the formula is seen as html here it is spelled out: =COUNTIF(Contact_Date,"Greater than="&DATE(2007,3,18)- COUNTIF(Contact_Date,"Less than="&DATE(2007,3,24))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A week of dates and counting them using the COUNTIF function with contingencies
On Mar 21, 3:23 pm, "Pete_UK" wrote:
Try this: =COUNTIF(Contact_Date,"="&DATE(2007,3,18)- COUNTIF(Contact_Date,""&DATE(2007,3,24))) The first COUNTIf will count everything with a date later than 17th March, and the second COUNTIF will count everything later than 25th March. You were counting if earlier than 25th March. Hope this helps. Pete On Mar 21, 7:33 pm, "j razz" wrote: =COUNTIF(Contact_Date,"="&DATE(2007,3,18)- COUNTIF(Contact_Date,"<="&DATE(2007,3,24))) The above is the formula I am attempting to use. Definitions: Contact_Date is a list of dates that range over 2000 cells that could span up to 1 year. In the example above I am wanting to find any dates that range between and include the dates: 3.18.2007 and 3.24.2007 Problem: The formula I am using is wanting to look past the dates provided and count any prior dates that do not fall within the specified range. I do not know why or how to fix it. Any ideas? In case the formula is seen as html here it is spelled out: =COUNT(IF((B1:B101$E$1)*(B1:B101<$E$2),B1:B101)) - Hide quoted text - - Show quoted text - Great! Thanks Pete. I appreciate the answer although I have moved to a different formula that produces the same correct result: =COUNT(IF((B1:B101$E$1)*(B1:B101<$E$2),B1:B101)) once entered in the formula bar you must press and hold ctrl, shift, and enter to get it to take effect. Now though I have another question, if I want to use the formula you provided and add one more variable to it how would that be done? For instance I would like to add to your formula: =COUNTIF(In_Coming_Contact,"="&DATE(2007,3,18)- COUNTIF(In_Coming_Contact,""&DATE(2007,3,24))) a variable. The variable being an item named AC in the dropdown list contained in the In_Coming_Contact range. I suppose I would need to nest the variable? I want it to count the amount of times AC shows up in the cell with the drop down options in the given dates. Thanks for the quick response! j razz |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A week of dates and counting them using the COUNTIF function with contingencies
Okay, I believe I have the formula for nesting the variable I want:
{=IF(In_Coming_Contact="AC", (COUNT(IF((Contact_Date=A4)*(Contact_Date<=B4),Co ntact_Date))))} Again, if anybody uses this, you will need to remove the { } brackets and when you enter the code in your formula bar press and hold ctrl and shift and then press Enter. I have tried it out and it appears to be working. j razz |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A week of dates and counting them using the COUNTIF function with contingencies
Are you sure you're getting the correct result with that formula?
See this screencap: http://img410.imageshack.us/img410/6273/sampleph7.jpg Try the formula you see in row 2. It's not an array entered formula so you don't have to worry about remembering the squiggly brackets { }. Biff "j razz" wrote in message ups.com... Okay, I believe I have the formula for nesting the variable I want: {=IF(In_Coming_Contact="AC", (COUNT(IF((Contact_Date=A4)*(Contact_Date<=B4),Co ntact_Date))))} Again, if anybody uses this, you will need to remove the { } brackets and when you enter the code in your formula bar press and hold ctrl and shift and then press Enter. I have tried it out and it appears to be working. j razz |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A week of dates and counting them using the COUNTIF function with contingencies
So much for that! It didn't work. It works okay if there are no other variables in the drop down. For some reason I cannot get an accurate response if I start changing the variables that populate the cell from the drop down. For instance, I have 5 or so variables contained in the drop down. AC is one, OIG is another. If I start mixing them in cells going down the column, the results gathered by the formula: {=IF(In_Coming_Contact="AC", (COUNT(IF((Contact_Date=A4)*(Contact_Date<=B4),Co ntact_Date))))} or by this formula {=IF(In_Coming_Contact="OIG", (COUNT(IF((Contact_Date=A4)*(Contact_Date<=B4),Co ntact_Date))))} cause unwanted results as it somehow does not utlize the portion of the formula that says if in coming contact equals ac then... or if in coming contact equals oig then... Can someone walk me through this? Thanks! j razz |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A week of dates and counting them using the COUNTIF function with contingencies
Thanks, that worked great. Would you mind telling me what I did wrong so I can learn? j razz |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A week of dates and counting them using the COUNTIF function with contingencies
On Mar 21, 5:57 pm, "j razz" wrote:
Thanks, that worked great. Would you mind telling me what I did wrong so I can learn? j razz Okay, I have another question. So, let's say that I want to nest the formula: =SUMPRODUCT(--(In_Coming_Contact="Other than client"),-- (Contact_Date=A3),--(Contact_Date<=B3)) with a contingency. I want to add a category on the front end that will allow me to know if these results are under CK, TNC or under another option which will also be pulled from a drop-down list called Application_Type. Thanks. j razz |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A week of dates and counting them using the COUNTIF function with contingencies
I answered my own question; I used this formula to produce the result
I wanted: =SUMPRODUCT(--(Application_Type="CK"),--(Contact_Date=A3),-- (Contact_Date<=B3)) j razz |
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) | |||
life contingencies | Excel Worksheet Functions | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
how do i sum day of the week without counting sats and sundays? | Excel Worksheet Functions | |||
Counting a day of the week | Excel Worksheet Functions |