Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Hello,
I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Hi
Assuming your dates are in column A and Times in column B in D2 Enter your week start date In E2 =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)*($B$2:B$1000TIME(0,5,0))) Will give the Count of Times Greater than 5 minutes in that week. If you enter in F2 =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)) That will give a Count of all times for that Week. G2 =E2/F2*100 If you made E3 =E2+7 then that would step up the starting week dates for you without you having to type them. Copy formulae down as required -- Regards Roger Govier "LoriB" wrote in message ... Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Hi Roger,
Thanks for the reply but the formula is returning an error. I'm not sure if it's because of a problem in the formula or that I'm using 2002. Or it could be that the time column is not formulated as time just as a number with 2 decimal places. I have =SUMPRODUCT(--(D2:D31719<="7/26/2008"+0),--(D2:D31719"7/19/2008"+0),G2:G31719) which finds the total but is more cumbersome than your formula as I am manually entering the date ranges. Thanks "Roger Govier" wrote: Hi Assuming your dates are in column A and Times in column B in D2 Enter your week start date In E2 =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)*($B$2:B$1000TIME(0,5,0))) Will give the Count of Times Greater than 5 minutes in that week. If you enter in F2 =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)) That will give a Count of all times for that Week. G2 =E2/F2*100 If you made E3 =E2+7 then that would step up the starting week dates for you without you having to type them. Copy formulae down as required -- Regards Roger Govier "LoriB" wrote in message ... Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Hi
then change the formula to =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)*($B$2:B$10005)) -- Regards Roger Govier "LoriB" wrote in message ... Hi Roger, Thanks for the reply but the formula is returning an error. I'm not sure if it's because of a problem in the formula or that I'm using 2002. Or it could be that the time column is not formulated as time just as a number with 2 decimal places. I have =SUMPRODUCT(--(D2:D31719<="7/26/2008"+0),--(D2:D31719"7/19/2008"+0),G2:G31719) which finds the total but is more cumbersome than your formula as I am manually entering the date ranges. Thanks "Roger Govier" wrote: Hi Assuming your dates are in column A and Times in column B in D2 Enter your week start date In E2 =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)*($B$2:B$1000TIME(0,5,0))) Will give the Count of Times Greater than 5 minutes in that week. If you enter in F2 =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)) That will give a Count of all times for that Week. G2 =E2/F2*100 If you made E3 =E2+7 then that would step up the starting week dates for you without you having to type them. Copy formulae down as required -- Regards Roger Govier "LoriB" wrote in message ... Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Hi,
Here is a formula that should get you the % of items in a given week that exceed 5 minutes: =SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2),--(B$2:B$265))/SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2)) A2:A26 is the date range, B2:B26 is the minute range. E1 contains the starting date of the week you want, E2 contains the ending date of that week. You can enter these each week to get your results. You will highlight a much larger range but it makes no difference. Format the formula cell to %. There is one possible complication, this formula assumes minutes are entered as numbers such as 1, 3, 11 or 2.5, but not as times 8:03. If this helps, please click the Yes button. Thanks, Shane Devenshire "LoriB" wrote: Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
I tried that, it didn't work either. I've been playing with the formula but
continually get errors or incorrect data. It might be summing and not counting. "Roger Govier" wrote: Hi then change the formula to =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)*($B$2:B$10005)) -- Regards Roger Govier "LoriB" wrote in message ... Hi Roger, Thanks for the reply but the formula is returning an error. I'm not sure if it's because of a problem in the formula or that I'm using 2002. Or it could be that the time column is not formulated as time just as a number with 2 decimal places. I have =SUMPRODUCT(--(D2:D31719<="7/26/2008"+0),--(D2:D31719"7/19/2008"+0),G2:G31719) which finds the total but is more cumbersome than your formula as I am manually entering the date ranges. Thanks "Roger Govier" wrote: Hi Assuming your dates are in column A and Times in column B in D2 Enter your week start date In E2 =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)*($B$2:B$1000TIME(0,5,0))) Will give the Count of Times Greater than 5 minutes in that week. If you enter in F2 =SUMPRODUCT(($A2$A$1000=$D2)* ($A$2:$A$1000<=$D2+6)) That will give a Count of all times for that Week. G2 =E2/F2*100 If you made E3 =E2+7 then that would step up the starting week dates for you without you having to type them. Copy formulae down as required -- Regards Roger Govier "LoriB" wrote in message ... Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Thanks for your help but I'm sorry, this formula returns incorrect result.
When I try in test data a COUNTIF statement finds 147, this formula finds none. Again, I wonder if the sumproduct is adding and not counting. My data looks like this: Date Minutes 7/1/2008 35.53 7/1/2008 6.78 7/1/2008 4.00 7/1/2008 32.43 7/1/2008 32.75 7/1/2008 25.68 7/1/2008 13.72 7/1/2008 18.33 7/1/2008 6.57 7/1/2008 8.65 7/1/2008 37.65 7/1/2008 27.43 7/1/2008 21.83 7/1/2008 19.55 I'm trying to find out how many times the time column shows less 5, in the example since it's 14 rows & only 1 is less than 5 the result returned should be 13. I hope that makes things clearer. Thank you for your help. "ShaneDevenshire" wrote: Hi, Here is a formula that should get you the % of items in a given week that exceed 5 minutes: =SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2),--(B$2:B$265))/SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2)) A2:A26 is the date range, B2:B26 is the minute range. E1 contains the starting date of the week you want, E2 contains the ending date of that week. You can enter these each week to get your results. You will highlight a much larger range but it makes no difference. Format the formula cell to %. There is one possible complication, this formula assumes minutes are entered as numbers such as 1, 3, 11 or 2.5, but not as times 8:03. If this helps, please click the Yes button. Thanks, Shane Devenshire "LoriB" wrote: Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Hi
Sorry, typo in my formula try =SUMPRODUCT(($A2:$A$1000=$D2)*($A$2:$A$1000<=$D2+ 6)*($B$2:B$10005)) Returned a result of 13 with your data. -- Regards Roger Govier "LoriB" wrote in message ... Thanks for your help but I'm sorry, this formula returns incorrect result. When I try in test data a COUNTIF statement finds 147, this formula finds none. Again, I wonder if the sumproduct is adding and not counting. My data looks like this: Date Minutes 7/1/2008 35.53 7/1/2008 6.78 7/1/2008 4.00 7/1/2008 32.43 7/1/2008 32.75 7/1/2008 25.68 7/1/2008 13.72 7/1/2008 18.33 7/1/2008 6.57 7/1/2008 8.65 7/1/2008 37.65 7/1/2008 27.43 7/1/2008 21.83 7/1/2008 19.55 I'm trying to find out how many times the time column shows less 5, in the example since it's 14 rows & only 1 is less than 5 the result returned should be 13. I hope that makes things clearer. Thank you for your help. "ShaneDevenshire" wrote: Hi, Here is a formula that should get you the % of items in a given week that exceed 5 minutes: =SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2),--(B$2:B$265))/SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2)) A2:A26 is the date range, B2:B26 is the minute range. E1 contains the starting date of the week you want, E2 contains the ending date of that week. You can enter these each week to get your results. You will highlight a much larger range but it makes no difference. Format the formula cell to %. There is one possible complication, this formula assumes minutes are entered as numbers such as 1, 3, 11 or 2.5, but not as times 8:03. If this helps, please click the Yes button. Thanks, Shane Devenshire "LoriB" wrote: Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Thanks Roger, that does work. The weird thing is that when I use that
formula I get different results compared to a report that uses the same data for 1 week and a COUNTIF formula (=COUNTIF(B2:B1000,"5") I do you have any idea why the results would be different? Thanks again "Roger Govier" wrote: Hi Sorry, typo in my formula try =SUMPRODUCT(($A2:$A$1000=$D2)*($A$2:$A$1000<=$D2+ 6)*($B$2:B$10005)) Returned a result of 13 with your data. -- Regards Roger Govier "LoriB" wrote in message ... Thanks for your help but I'm sorry, this formula returns incorrect result. When I try in test data a COUNTIF statement finds 147, this formula finds none. Again, I wonder if the sumproduct is adding and not counting. My data looks like this: Date Minutes 7/1/2008 35.53 7/1/2008 6.78 7/1/2008 4.00 7/1/2008 32.43 7/1/2008 32.75 7/1/2008 25.68 7/1/2008 13.72 7/1/2008 18.33 7/1/2008 6.57 7/1/2008 8.65 7/1/2008 37.65 7/1/2008 27.43 7/1/2008 21.83 7/1/2008 19.55 I'm trying to find out how many times the time column shows less 5, in the example since it's 14 rows & only 1 is less than 5 the result returned should be 13. I hope that makes things clearer. Thank you for your help. "ShaneDevenshire" wrote: Hi, Here is a formula that should get you the % of items in a given week that exceed 5 minutes: =SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2),--(B$2:B$265))/SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2)) A2:A26 is the date range, B2:B26 is the minute range. E1 contains the starting date of the week you want, E2 contains the ending date of that week. You can enter these each week to get your results. You will highlight a much larger range but it makes no difference. Format the formula cell to %. There is one possible complication, this formula assumes minutes are entered as numbers such as 1, 3, 11 or 2.5, but not as times 8:03. If this helps, please click the Yes button. Thanks, Shane Devenshire "LoriB" wrote: Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Hi
If the data is the same, the results will be the same. Perhaps you have some data which is outside the week? Perhaps you have a Text value of 5 rather than numeric in one of the rows? -- Regards Roger Govier "LoriB" wrote in message ... Thanks Roger, that does work. The weird thing is that when I use that formula I get different results compared to a report that uses the same data for 1 week and a COUNTIF formula (=COUNTIF(B2:B1000,"5") I do you have any idea why the results would be different? Thanks again "Roger Govier" wrote: Hi Sorry, typo in my formula try =SUMPRODUCT(($A2:$A$1000=$D2)*($A$2:$A$1000<=$D2+ 6)*($B$2:B$10005)) Returned a result of 13 with your data. -- Regards Roger Govier "LoriB" wrote in message ... Thanks for your help but I'm sorry, this formula returns incorrect result. When I try in test data a COUNTIF statement finds 147, this formula finds none. Again, I wonder if the sumproduct is adding and not counting. My data looks like this: Date Minutes 7/1/2008 35.53 7/1/2008 6.78 7/1/2008 4.00 7/1/2008 32.43 7/1/2008 32.75 7/1/2008 25.68 7/1/2008 13.72 7/1/2008 18.33 7/1/2008 6.57 7/1/2008 8.65 7/1/2008 37.65 7/1/2008 27.43 7/1/2008 21.83 7/1/2008 19.55 I'm trying to find out how many times the time column shows less 5, in the example since it's 14 rows & only 1 is less than 5 the result returned should be 13. I hope that makes things clearer. Thank you for your help. "ShaneDevenshire" wrote: Hi, Here is a formula that should get you the % of items in a given week that exceed 5 minutes: =SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2),--(B$2:B$265))/SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2)) A2:A26 is the date range, B2:B26 is the minute range. E1 contains the starting date of the week you want, E2 contains the ending date of that week. You can enter these each week to get your results. You will highlight a much larger range but it makes no difference. Format the formula cell to %. There is one possible complication, this formula assumes minutes are entered as numbers such as 1, 3, 11 or 2.5, but not as times 8:03. If this helps, please click the Yes button. Thanks, Shane Devenshire "LoriB" wrote: Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
I fond the problem, thanks again for all your help.
"Roger Govier" wrote: Hi If the data is the same, the results will be the same. Perhaps you have some data which is outside the week? Perhaps you have a Text value of 5 rather than numeric in one of the rows? -- Regards Roger Govier "LoriB" wrote in message ... Thanks Roger, that does work. The weird thing is that when I use that formula I get different results compared to a report that uses the same data for 1 week and a COUNTIF formula (=COUNTIF(B2:B1000,"5") I do you have any idea why the results would be different? Thanks again "Roger Govier" wrote: Hi Sorry, typo in my formula try =SUMPRODUCT(($A2:$A$1000=$D2)*($A$2:$A$1000<=$D2+ 6)*($B$2:B$10005)) Returned a result of 13 with your data. -- Regards Roger Govier "LoriB" wrote in message ... Thanks for your help but I'm sorry, this formula returns incorrect result. When I try in test data a COUNTIF statement finds 147, this formula finds none. Again, I wonder if the sumproduct is adding and not counting. My data looks like this: Date Minutes 7/1/2008 35.53 7/1/2008 6.78 7/1/2008 4.00 7/1/2008 32.43 7/1/2008 32.75 7/1/2008 25.68 7/1/2008 13.72 7/1/2008 18.33 7/1/2008 6.57 7/1/2008 8.65 7/1/2008 37.65 7/1/2008 27.43 7/1/2008 21.83 7/1/2008 19.55 I'm trying to find out how many times the time column shows less 5, in the example since it's 14 rows & only 1 is less than 5 the result returned should be 13. I hope that makes things clearer. Thank you for your help. "ShaneDevenshire" wrote: Hi, Here is a formula that should get you the % of items in a given week that exceed 5 minutes: =SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2),--(B$2:B$265))/SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2)) A2:A26 is the date range, B2:B26 is the minute range. E1 contains the starting date of the week you want, E2 contains the ending date of that week. You can enter these each week to get your results. You will highlight a much larger range but it makes no difference. Format the formula cell to %. There is one possible complication, this formula assumes minutes are entered as numbers such as 1, 3, 11 or 2.5, but not as times 8:03. If this helps, please click the Yes button. Thanks, Shane Devenshire "LoriB" wrote: Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
For the benefit of others, what was the problem?
-- Regards Roger Govier "LoriB" wrote in message ... I fond the problem, thanks again for all your help. "Roger Govier" wrote: Hi If the data is the same, the results will be the same. Perhaps you have some data which is outside the week? Perhaps you have a Text value of 5 rather than numeric in one of the rows? -- Regards Roger Govier "LoriB" wrote in message ... Thanks Roger, that does work. The weird thing is that when I use that formula I get different results compared to a report that uses the same data for 1 week and a COUNTIF formula (=COUNTIF(B2:B1000,"5") I do you have any idea why the results would be different? Thanks again "Roger Govier" wrote: Hi Sorry, typo in my formula try =SUMPRODUCT(($A2:$A$1000=$D2)*($A$2:$A$1000<=$D2+ 6)*($B$2:B$10005)) Returned a result of 13 with your data. -- Regards Roger Govier "LoriB" wrote in message ... Thanks for your help but I'm sorry, this formula returns incorrect result. When I try in test data a COUNTIF statement finds 147, this formula finds none. Again, I wonder if the sumproduct is adding and not counting. My data looks like this: Date Minutes 7/1/2008 35.53 7/1/2008 6.78 7/1/2008 4.00 7/1/2008 32.43 7/1/2008 32.75 7/1/2008 25.68 7/1/2008 13.72 7/1/2008 18.33 7/1/2008 6.57 7/1/2008 8.65 7/1/2008 37.65 7/1/2008 27.43 7/1/2008 21.83 7/1/2008 19.55 I'm trying to find out how many times the time column shows less 5, in the example since it's 14 rows & only 1 is less than 5 the result returned should be 13. I hope that makes things clearer. Thank you for your help. "ShaneDevenshire" wrote: Hi, Here is a formula that should get you the % of items in a given week that exceed 5 minutes: =SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2),--(B$2:B$265))/SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2)) A2:A26 is the date range, B2:B26 is the minute range. E1 contains the starting date of the week you want, E2 contains the ending date of that week. You can enter these each week to get your results. You will highlight a much larger range but it makes no difference. Format the formula cell to %. There is one possible complication, this formula assumes minutes are entered as numbers such as 1, 3, 11 or 2.5, but not as times 8:03. If this helps, please click the Yes button. Thanks, Shane Devenshire "LoriB" wrote: Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF by date range problem
Text value rather than # as you suggested.
Thanks "Roger Govier" wrote: For the benefit of others, what was the problem? -- Regards Roger Govier "LoriB" wrote in message ... I fond the problem, thanks again for all your help. "Roger Govier" wrote: Hi If the data is the same, the results will be the same. Perhaps you have some data which is outside the week? Perhaps you have a Text value of 5 rather than numeric in one of the rows? -- Regards Roger Govier "LoriB" wrote in message ... Thanks Roger, that does work. The weird thing is that when I use that formula I get different results compared to a report that uses the same data for 1 week and a COUNTIF formula (=COUNTIF(B2:B1000,"5") I do you have any idea why the results would be different? Thanks again "Roger Govier" wrote: Hi Sorry, typo in my formula try =SUMPRODUCT(($A2:$A$1000=$D2)*($A$2:$A$1000<=$D2+ 6)*($B$2:B$10005)) Returned a result of 13 with your data. -- Regards Roger Govier "LoriB" wrote in message ... Thanks for your help but I'm sorry, this formula returns incorrect result. When I try in test data a COUNTIF statement finds 147, this formula finds none. Again, I wonder if the sumproduct is adding and not counting. My data looks like this: Date Minutes 7/1/2008 35.53 7/1/2008 6.78 7/1/2008 4.00 7/1/2008 32.43 7/1/2008 32.75 7/1/2008 25.68 7/1/2008 13.72 7/1/2008 18.33 7/1/2008 6.57 7/1/2008 8.65 7/1/2008 37.65 7/1/2008 27.43 7/1/2008 21.83 7/1/2008 19.55 I'm trying to find out how many times the time column shows less 5, in the example since it's 14 rows & only 1 is less than 5 the result returned should be 13. I hope that makes things clearer. Thank you for your help. "ShaneDevenshire" wrote: Hi, Here is a formula that should get you the % of items in a given week that exceed 5 minutes: =SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2),--(B$2:B$265))/SUMPRODUCT(--(A$2:A$26=E1),--(A$2:A$26<=E2)) A2:A26 is the date range, B2:B26 is the minute range. E1 contains the starting date of the week you want, E2 contains the ending date of that week. You can enter these each week to get your results. You will highlight a much larger range but it makes no difference. Format the formula cell to %. There is one possible complication, this formula assumes minutes are entered as numbers such as 1, 3, 11 or 2.5, but not as times 8:03. If this helps, please click the Yes button. Thanks, Shane Devenshire "LoriB" wrote: Hello, I have a sheet that shows data for several months, it has multiple instances of each day (7/1/2008 may appear in 40 rows) as the data is from different sources. It has another column that shows the amount of time an even happened for each date in minutes. I need to filter this information by week and by the count of times 5 minutes to get the percent of time 5 minutes this even happened each week. Just to make matters more difficult the data will be updated each week, thankfully it will be added to the end of the table so I can just past down the formula. I'd really appreciate some help with this because a COUNTIF formula keeps returning errors. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif function - using a date range | Excel Worksheet Functions | |||
Countif with date range criteria | Excel Worksheet Functions | |||
countif date range | Excel Worksheet Functions | |||
SumProduct/CountIf dilemna + Date Range | Excel Worksheet Functions | |||
use a date range as criteria in a countif formula | Excel Worksheet Functions |