| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
criteria in multiple columns
I am trying to get a count of rows where the date column has a specific date
and a second column is marked with an "X". Example: Col F is a column of dates Col L is marked with an "X" by the end user if certain events have taken place. Please help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
criteria in multiple columns
Try this:
=SUMPRODUCT(--(F1:F10=DATE(2007,1,1)),--(L1:L10="X")) Or, use cells to hold the criteria: A1 = some date like 1/1/2007 B1 = X =SUMPRODUCT(--(F1:F10=A1),--(L1:L10=B1)) Biff "mbparks" wrote in message ... I am trying to get a count of rows where the date column has a specific date and a second column is marked with an "X". Example: Col F is a column of dates Col L is marked with an "X" by the end user if certain events have taken place. Please help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
criteria in multiple columns
The =SUMPRODUCT works perfectly. You are the best. Thank you so much!
"T. Valko" wrote: Try this: =SUMPRODUCT(--(F1:F10=DATE(2007,1,1)),--(L1:L10="X")) Or, use cells to hold the criteria: A1 = some date like 1/1/2007 B1 = X =SUMPRODUCT(--(F1:F10=A1),--(L1:L10=B1)) Biff "mbparks" wrote in message ... I am trying to get a count of rows where the date column has a specific date and a second column is marked with an "X". Example: Col F is a column of dates Col L is marked with an "X" by the end user if certain events have taken place. Please help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
criteria in multiple columns
You're welcome. Thanks for the feedback!
Biff "mbparks" wrote in message ... The =SUMPRODUCT works perfectly. You are the best. Thank you so much! "T. Valko" wrote: Try this: =SUMPRODUCT(--(F1:F10=DATE(2007,1,1)),--(L1:L10="X")) Or, use cells to hold the criteria: A1 = some date like 1/1/2007 B1 = X =SUMPRODUCT(--(F1:F10=A1),--(L1:L10=B1)) Biff "mbparks" wrote in message ... I am trying to get a count of rows where the date column has a specific date and a second column is marked with an "X". Example: Col F is a column of dates Col L is marked with an "X" by the end user if certain events have taken place. Please help. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
criteria in multiple columns
Can you please tell me how to make this work with a date range (ex: 3/1/07 -
3/31/07) also? "T. Valko" wrote: Try this: =SUMPRODUCT(--(F1:F10=DATE(2007,1,1)),--(L1:L10="X")) Or, use cells to hold the criteria: A1 = some date like 1/1/2007 B1 = X =SUMPRODUCT(--(F1:F10=A1),--(L1:L10=B1)) Biff "mbparks" wrote in message ... I am trying to get a count of rows where the date column has a specific date and a second column is marked with an "X". Example: Col F is a column of dates Col L is marked with an "X" by the end user if certain events have taken place. Please help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
criteria in multiple columns
Try one of these:
=SUMPRODUCT(--(F1:F10=DATE(2007,3,1)),--(F1:F10<=DATE(2007,3,31)),--(L1:L10="X")) Or: A1 = start date 3/1/2007 B1 = end date 3/31/2007 C1 = X =SUMPRODUCT(--(F1:F10=A1),--(F1:F10<=B1),--(L1:L10=C1)) Biff "mbparks" wrote in message ... Can you please tell me how to make this work with a date range (ex: 3/1/07 - 3/31/07) also? "T. Valko" wrote: Try this: =SUMPRODUCT(--(F1:F10=DATE(2007,1,1)),--(L1:L10="X")) Or, use cells to hold the criteria: A1 = some date like 1/1/2007 B1 = X =SUMPRODUCT(--(F1:F10=A1),--(L1:L10=B1)) Biff "mbparks" wrote in message ... I am trying to get a count of rows where the date column has a specific date and a second column is marked with an "X". Example: Col F is a column of dates Col L is marked with an "X" by the end user if certain events have taken place. Please help. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
criteria in multiple columns
Once again you are a lifesaver! Thanks
"T. Valko" wrote: Try one of these: =SUMPRODUCT(--(F1:F10=DATE(2007,3,1)),--(F1:F10<=DATE(2007,3,31)),--(L1:L10="X")) Or: A1 = start date 3/1/2007 B1 = end date 3/31/2007 C1 = X =SUMPRODUCT(--(F1:F10=A1),--(F1:F10<=B1),--(L1:L10=C1)) Biff "mbparks" wrote in message ... Can you please tell me how to make this work with a date range (ex: 3/1/07 - 3/31/07) also? "T. Valko" wrote: Try this: =SUMPRODUCT(--(F1:F10=DATE(2007,1,1)),--(L1:L10="X")) Or, use cells to hold the criteria: A1 = some date like 1/1/2007 B1 = X =SUMPRODUCT(--(F1:F10=A1),--(L1:L10=B1)) Biff "mbparks" wrote in message ... I am trying to get a count of rows where the date column has a specific date and a second column is marked with an "X". Example: Col F is a column of dates Col L is marked with an "X" by the end user if certain events have taken place. Please help. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
criteria in multiple columns
One way:
=SUMPRODUCT(($F$2:$F$100=--"01-Jan-2007")*($L$2:$L$100="X")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mbparks" wrote: I am trying to get a count of rows where the date column has a specific date and a second column is marked with an "X". Example: Col F is a column of dates Col L is marked with an "X" by the end user if certain events have taken place. Please help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF, One criteria multiple columns | Excel Discussion (Misc queries) | |||
| SUMIF - multiple criteria in different columns | Excel Worksheet Functions | |||
| Countif using criteria in multiple columns | Excel Worksheet Functions | |||
| SUMIF with multiple criteria, by columns | Excel Worksheet Functions | |||
| How can I count items in multiple columns with different criteria. | Excel Worksheet Functions | |||
Hybrid Mode
