Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Multiple Range Criteria (Excel 2003)
I'm trying to count the number of rows that matches multiple criteria in
multiple ranges. As an example, I want to count the number of rows where both Column A and Column B have a date between 01-01-05 and 12-31-05. Whe Column A Column B 01-02-05 12-20-05 01-06-05 02-20-06 05-06-09 03-07-05 01-02-06 01-09-06 So, in this instance, I want it to count only row 1 because both columns match the criteria. I've tried a couple of different Countif statements, but I can never get it to count using the multiple criteria and ranges. Please assist. Things I've tried: =Countif(and(A:A,B:B), ""&C1))+countif(and(A:A,B:B), "<"&D1) - - where C1 is equal to 01-01-05 and D1 is 12-31-05 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Multiple Range Criteria (Excel 2003)
=SUMPRODUCT(--(A2:A100C1),--(A2:A100<D1),--(B2:B100C1),--(B2:B100<D1))
Note that unless you are using XL 2007, you can't callout the entire column within SUMPRODUCT. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jennifer" wrote: I'm trying to count the number of rows that matches multiple criteria in multiple ranges. As an example, I want to count the number of rows where both Column A and Column B have a date between 01-01-05 and 12-31-05. Whe Column A Column B 01-02-05 12-20-05 01-06-05 02-20-06 05-06-09 03-07-05 01-02-06 01-09-06 So, in this instance, I want it to count only row 1 because both columns match the criteria. I've tried a couple of different Countif statements, but I can never get it to count using the multiple criteria and ranges. Please assist. Things I've tried: =Countif(and(A:A,B:B), ""&C1))+countif(and(A:A,B:B), "<"&D1) - - where C1 is equal to 01-01-05 and D1 is 12-31-05 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Multiple Range Criteria (Excel 2003)
Luke - this is returning an error when I enter it in exactly like you have
it. When I change the "--" to "-", I get a returned value of 1, so it's still not pulling it correctly. "Luke M" wrote: =SUMPRODUCT(--(A2:A100C1),--(A2:A100<D1),--(B2:B100C1),--(B2:B100<D1)) Note that unless you are using XL 2007, you can't callout the entire column within SUMPRODUCT. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jennifer" wrote: I'm trying to count the number of rows that matches multiple criteria in multiple ranges. As an example, I want to count the number of rows where both Column A and Column B have a date between 01-01-05 and 12-31-05. Whe Column A Column B 01-02-05 12-20-05 01-06-05 02-20-06 05-06-09 03-07-05 01-02-06 01-09-06 So, in this instance, I want it to count only row 1 because both columns match the criteria. I've tried a couple of different Countif statements, but I can never get it to count using the multiple criteria and ranges. Please assist. Things I've tried: =Countif(and(A:A,B:B), ""&C1))+countif(and(A:A,B:B), "<"&D1) - - where C1 is equal to 01-01-05 and D1 is 12-31-05 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Multiple Range Criteria (Excel 2003)
.. I want to count the number of rows where both Column A
and Column B have a date between 01-01-05 and 12-31-05 An alternative to try, presuming data in cols A and B are all real dates: =SUMPRODUCT((TEXT(A1:A4,"yyyy")="2005")*(TEXT(B1:B 4,"yyyy")="2005")) Adapt the ranges to suit the actual extents. Any joy? hit the YES below -- Max Singapore xde --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Multiple Range Criteria (Excel 2003)
That will only work for that particular scenario...I also need it to work for
date ranges between 02-01-05 and 01-31-06, so I can't limit it to just the year. "Max" wrote: .. I want to count the number of rows where both Column A and Column B have a date between 01-01-05 and 12-31-05 An alternative to try, presuming data in cols A and B are all real dates: =SUMPRODUCT((TEXT(A1:A4,"yyyy")="2005")*(TEXT(B1:B 4,"yyyy")="2005")) Adapt the ranges to suit the actual extents. Any joy? hit the YES below -- Max Singapore xde --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Multiple Range Criteria (Excel 2003)
.. I also need it to work for date ranges between 02-01-05 and 01-31-06,
so I can't limit it to just the year Try it like this then, with explicit, unambiguous date ranges defined: =SUMPRODUCT((A1:A4=--"2 Jan 2005")*(A1:A4<=--"31 Jan 2006")*(B1:B4=--"2 Jan 2005")*(B1:B4<=--"31 Jan 2006")) Success? hit the YES below -- Max Singapore xde --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Multiple Range Criteria (Excel 2003)
What error is it spitting out? To explain, the "--" is there to convert the
results from the comparison (true/false) into numbers. The first "-" converts it to (-1/0) and the second "-" converts it to (1/0). The 4 arrays are then multipied across each other, and only when four 1's are found is an entry counted. Playing around with the formula as written, I am unable to get it to create an error (even when text is used in some of the fields). Possible debugging method would be to change the arrays that are being examined until you find the area that is causing trouble? Of final note, the array "sizes" must be identical. For instance, in the formula I gave all arrays are 99 rows. Sorry this isn't more helpful. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jennifer" wrote: Luke - this is returning an error when I enter it in exactly like you have it. When I change the "--" to "-", I get a returned value of 1, so it's still not pulling it correctly. "Luke M" wrote: =SUMPRODUCT(--(A2:A100C1),--(A2:A100<D1),--(B2:B100C1),--(B2:B100<D1)) Note that unless you are using XL 2007, you can't callout the entire column within SUMPRODUCT. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jennifer" wrote: I'm trying to count the number of rows that matches multiple criteria in multiple ranges. As an example, I want to count the number of rows where both Column A and Column B have a date between 01-01-05 and 12-31-05. Whe Column A Column B 01-02-05 12-20-05 01-06-05 02-20-06 05-06-09 03-07-05 01-02-06 01-09-06 So, in this instance, I want it to count only row 1 because both columns match the criteria. I've tried a couple of different Countif statements, but I can never get it to count using the multiple criteria and ranges. Please assist. Things I've tried: =Countif(and(A:A,B:B), ""&C1))+countif(and(A:A,B:B), "<"&D1) - - where C1 is equal to 01-01-05 and D1 is 12-31-05 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with Multiple Range Criteria (Excel 2003)
What error is it reporting?
The formula is fine, so it sounds as if you tried to retype the formula and got it wrong. Rather than retyping, copy it from the newsgroup and paste into the formula bar on your spreadsheet. -- David Biddulph "Jennifer" wrote in message ... Luke - this is returning an error when I enter it in exactly like you have it. When I change the "--" to "-", I get a returned value of 1, so it's still not pulling it correctly. "Luke M" wrote: =SUMPRODUCT(--(A2:A100C1),--(A2:A100<D1),--(B2:B100C1),--(B2:B100<D1)) Note that unless you are using XL 2007, you can't callout the entire column within SUMPRODUCT. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jennifer" wrote: I'm trying to count the number of rows that matches multiple criteria in multiple ranges. As an example, I want to count the number of rows where both Column A and Column B have a date between 01-01-05 and 12-31-05. Whe Column A Column B 01-02-05 12-20-05 01-06-05 02-20-06 05-06-09 03-07-05 01-02-06 01-09-06 So, in this instance, I want it to count only row 1 because both columns match the criteria. I've tried a couple of different Countif statements, but I can never get it to count using the multiple criteria and ranges. Please assist. Things I've tried: =Countif(and(A:A,B:B), ""&C1))+countif(and(A:A,B:B), "<"&D1) - - where C1 is equal to 01-01-05 and D1 is 12-31-05 |
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) | |||
Counting cells in a range per multiple criteria . . . | Excel Worksheet Functions | |||
Counting from one range to another range, multiple criteria | Excel Discussion (Misc queries) | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |