| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting number of rows between date range and meeting 2 string criteria
I would like to count the number of occurrences within a date range for
which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 string cr
There's prb a way to do this using COUNTIFS, but as I'm personally more
familiar w/ SUMPRODUCT, you could do: =SUMPRODUCT(--(Dates=DATE(2008,1,1)),--(Dates<=DATE(2008,2,1)),--((Categories="BOOK")+(Categories="PRI"))) Note that for the second half where you have multiple possible criteria, you are adding the arrays together (in case you want to add other criteria). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 string cr
Hi,
=IF(A:A=2008-1-1,SUMPRODUCT((B:B="Book")+(B:B="PRI"))) "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 string cr
Thanks.
I tried your formula, it turned out #VALUE! as well. So, I feel the problem can't be with the formula. I then use the A5:A500 in place of Dates and B5:B5000 in place of Categories, then it works. A check in the named range, I found that Dates is A5:A500 and Categories is B4:B5000, where B4 is the header. Is it that the two columns must have the same number of row? "Luke M" wrote in message ... There's prb a way to do this using COUNTIFS, but as I'm personally more familiar w/ SUMPRODUCT, you could do: =SUMPRODUCT(--(Dates=DATE(2008,1,1)),--(Dates<=DATE(2008,2,1)),--((Categories="BOOK")+(Categories="PRI"))) Note that for the second half where you have multiple possible criteria, you are adding the arrays together (in case you want to add other criteria). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 string cr
Thanks.
With =IF(A:A=2008-1-1,sumproduct((B:B="BOOK")+(B:B="PRI"))), it works fine. When I add additional condition - =IF(and(A:A=2008-1-1,A:A<2008-2-1),SUMPRODUCT((B:B="Book")+(B:B="PRI"))), it produces "FALSE". Why is it so? "Eduardo" wrote in message ... Hi, =IF(A:A=2008-1-1,SUMPRODUCT((B:B="Book")+(B:B="PRI"))) "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 string cr
Because 2008-1-1 is not recognized as a date. Excel calculates it as 2008
less 1 less 1 which equals 2006. To compare dates, you need to use either the Date or DateValue function. Also you can't compare a whole column with an If statement. You need Sumproduct to do that. Try: SUMPRODUCT(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--(B:B="Book")+(B:B="PRI"))) or, more simply: SUMPRODUCT(--(text(A:A,"yyyy-mm")="2008-01"),--(B:B="Book")+(B:B="PRI")) Regards, Fred "Hii Sing Chung" wrote in message ... Thanks. With =IF(A:A=2008-1-1,sumproduct((B:B="BOOK")+(B:B="PRI"))), it works fine. When I add additional condition - =IF(and(A:A=2008-1-1,A:A<2008-2-1),SUMPRODUCT((B:B="Book")+(B:B="PRI"))), it produces "FALSE". Why is it so? "Eduardo" wrote in message ... Hi, =IF(A:A=2008-1-1,SUMPRODUCT((B:B="Book")+(B:B="PRI"))) "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 strin
Thanks for the feedback, have a great day
"Hii Sing Chung" wrote: Thanks. With =IF(A:A=2008-1-1,sumproduct((B:B="BOOK")+(B:B="PRI"))), it works fine. When I add additional condition - =IF(and(A:A=2008-1-1,A:A<2008-2-1),SUMPRODUCT((B:B="Book")+(B:B="PRI"))), it produces "FALSE". Why is it so? "Eduardo" wrote in message ... Hi, =IF(A:A=2008-1-1,SUMPRODUCT((B:B="Book")+(B:B="PRI"))) "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 strin
Yes, both arrays/ranges must have same amount of rows.
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hii Sing Chung" wrote: Thanks. I tried your formula, it turned out #VALUE! as well. So, I feel the problem can't be with the formula. I then use the A5:A500 in place of Dates and B5:B5000 in place of Categories, then it works. A check in the named range, I found that Dates is A5:A500 and Categories is B4:B5000, where B4 is the header. Is it that the two columns must have the same number of row? "Luke M" wrote in message ... There's prb a way to do this using COUNTIFS, but as I'm personally more familiar w/ SUMPRODUCT, you could do: =SUMPRODUCT(--(Dates=DATE(2008,1,1)),--(Dates<=DATE(2008,2,1)),--((Categories="BOOK")+(Categories="PRI"))) Note that for the second half where you have multiple possible criteria, you are adding the arrays together (in case you want to add other criteria). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 string cr
Thanks.
The first formula seems to be missing a bracket. =sumproduct(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI"))) gives #Value! error =sumproduct(--(A:A=Date(2008,1,1))+(A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI"))) gives incorrect result The second formula works. "Fred Smith" wrote in message ... Because 2008-1-1 is not recognized as a date. Excel calculates it as 2008 less 1 less 1 which equals 2006. To compare dates, you need to use either the Date or DateValue function. Also you can't compare a whole column with an If statement. You need Sumproduct to do that. Try: SUMPRODUCT(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--(B:B="Book")+(B:B="PRI"))) or, more simply: SUMPRODUCT(--(text(A:A,"yyyy-mm")="2008-01"),--(B:B="Book")+(B:B="PRI")) Regards, Fred "Hii Sing Chung" wrote in message ... Thanks. With =IF(A:A=2008-1-1,sumproduct((B:B="BOOK")+(B:B="PRI"))), it works fine. When I add additional condition - =IF(and(A:A=2008-1-1,A:A<2008-2-1),SUMPRODUCT((B:B="Book")+(B:B="PRI"))), it produces "FALSE". Why is it so? "Eduardo" wrote in message ... Hi, =IF(A:A=2008-1-1,SUMPRODUCT((B:B="Book")+(B:B="PRI"))) "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 string cr
Perhaps
=sumproduct(--(A:A=Date(2008,1,1)),--(A:A<=Date(2008,2,1)),(B:B="Book")+(B:B="PRI")) ? or =sumproduct((A:A=Date(2008,1,1))*(A:A<=Date(2008, 2,1))*((B:B="Book")+(B:B="PRI"))) ? Remember that you don't need a double unary minus if you've got an addition anyway, such as ((B:B="Book")+(B:B="PRI")) -- David Biddulph "Hii Sing Chung" wrote in message ... Thanks. The first formula seems to be missing a bracket. =sumproduct(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI"))) gives #Value! error =sumproduct(--(A:A=Date(2008,1,1))+(A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI"))) gives incorrect result The second formula works. "Fred Smith" wrote in message ... Because 2008-1-1 is not recognized as a date. Excel calculates it as 2008 less 1 less 1 which equals 2006. To compare dates, you need to use either the Date or DateValue function. Also you can't compare a whole column with an If statement. You need Sumproduct to do that. Try: SUMPRODUCT(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--(B:B="Book")+(B:B="PRI"))) or, more simply: SUMPRODUCT(--(text(A:A,"yyyy-mm")="2008-01"),--(B:B="Book")+(B:B="PRI")) Regards, Fred "Hii Sing Chung" wrote in message ... Thanks. With =IF(A:A=2008-1-1,sumproduct((B:B="BOOK")+(B:B="PRI"))), it works fine. When I add additional condition - =IF(and(A:A=2008-1-1,A:A<2008-2-1),SUMPRODUCT((B:B="Book")+(B:B="PRI"))), it produces "FALSE". Why is it so? "Eduardo" wrote in message ... Hi, =IF(A:A=2008-1-1,SUMPRODUCT((B:B="Book")+(B:B="PRI"))) "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 string cr
You're welcome. Thanks for the feedback.
Regards, Fred. "Hii Sing Chung" wrote in message ... Thanks. The first formula seems to be missing a bracket. =sumproduct(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI"))) gives #Value! error =sumproduct(--(A:A=Date(2008,1,1))+(A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI"))) gives incorrect result The second formula works. "Fred Smith" wrote in message ... Because 2008-1-1 is not recognized as a date. Excel calculates it as 2008 less 1 less 1 which equals 2006. To compare dates, you need to use either the Date or DateValue function. Also you can't compare a whole column with an If statement. You need Sumproduct to do that. Try: SUMPRODUCT(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--(B:B="Book")+(B:B="PRI"))) or, more simply: SUMPRODUCT(--(text(A:A,"yyyy-mm")="2008-01"),--(B:B="Book")+(B:B="PRI")) Regards, Fred "Hii Sing Chung" wrote in message ... Thanks. With =IF(A:A=2008-1-1,sumproduct((B:B="BOOK")+(B:B="PRI"))), it works fine. When I add additional condition - =IF(and(A:A=2008-1-1,A:A<2008-2-1),SUMPRODUCT((B:B="Book")+(B:B="PRI"))), it produces "FALSE". Why is it so? "Eduardo" wrote in message ... Hi, =IF(A:A=2008-1-1,SUMPRODUCT((B:B="Book")+(B:B="PRI"))) "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of rows between date range and meeting 2 string cr
Thanks.
Both formulae work. The only correction is the second criteria should be A:A<Date(2008,2,1). "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Perhaps =sumproduct(--(A:A=Date(2008,1,1)),--(A:A<=Date(2008,2,1)),(B:B="Book")+(B:B="PRI")) ? or =sumproduct((A:A=Date(2008,1,1))*(A:A<=Date(2008, 2,1))*((B:B="Book")+(B:B="PRI"))) ? Remember that you don't need a double unary minus if you've got an addition anyway, such as ((B:B="Book")+(B:B="PRI")) -- David Biddulph "Hii Sing Chung" wrote in message ... Thanks. The first formula seems to be missing a bracket. =sumproduct(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI"))) gives #Value! error =sumproduct(--(A:A=Date(2008,1,1))+(A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI"))) gives incorrect result The second formula works. "Fred Smith" wrote in message ... Because 2008-1-1 is not recognized as a date. Excel calculates it as 2008 less 1 less 1 which equals 2006. To compare dates, you need to use either the Date or DateValue function. Also you can't compare a whole column with an If statement. You need Sumproduct to do that. Try: SUMPRODUCT(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--(B:B="Book")+(B:B="PRI"))) or, more simply: SUMPRODUCT(--(text(A:A,"yyyy-mm")="2008-01"),--(B:B="Book")+(B:B="PRI")) Regards, Fred "Hii Sing Chung" wrote in message ... Thanks. With =IF(A:A=2008-1-1,sumproduct((B:B="BOOK")+(B:B="PRI"))), it works fine. When I add additional condition - =IF(and(A:A=2008-1-1,A:A<2008-2-1),SUMPRODUCT((B:B="Book")+(B:B="PRI"))), it produces "FALSE". Why is it so? "Eduardo" wrote in message ... Hi, =IF(A:A=2008-1-1,SUMPRODUCT((B:B="Book")+(B:B="PRI"))) "Hii Sing Chung" wrote: I would like to count the number of occurrences within a date range for which the category belong to "BOOK" and "PRI", data examples as show below: Dates Categories 01-Mar-09 VCD 08-Feb-09 CDO 08-Feb-09 CDO 08-Feb-09 CDO 23-Nov-08 BOOK 08-Feb-09 BOOK 08-Feb-09 BOOK 06-Jul-08 PRI I am using Excel 2007 Dates is the named Range for column A, and Categories the named range for Column B. I had tried this formula, results in #VALUE! =SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI")) The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is already giving #VALUE! Is it that the criteria must be of the same datatype? If I count just 1 column, it is working fine, example: =COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works fine, and =SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK. I now using the Autofilters on the 2 columns to do the counting - subtotal(3, A1:A1). It becomes tedious when I need to do a few combination of date range and categories. Thanks in advance. Sing Chung |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting a number of cells with 2 range criteria | Excel Discussion (Misc queries) | |||
| FInding the largest number in a range meeting a criteria | Excel Discussion (Misc queries) | |||
| Sum within a date range meeting one other criteria | Excel Worksheet Functions | |||
| Counting distinct entries based on meeting month & year criteria | Excel Worksheet Functions | |||
| Counting the number of cells meeting conditional formating criteria | Excel Worksheet Functions | |||
Linear Mode
