Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
Col C = Text and Col F = dates
I would like to count the # of times a value occurs in Col C based on a date range in Col F. Does anybody have an answer to this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
Sounds like a job for SUMPRODUCT but note you cannot reference the
entire column using this function. Assume that A1 is the date you are using as criteria and A2 is the value you are trying to find in Col F. =SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2))) How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE values where the "--" turns them into 1 for TRUE and 0 for FALSE. F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each pair of 1's and 0's then adds them up. So if the first pair (C1 and F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the second pair are both TRUE the result would be 1 x 1 or 1. Add up all of the ones and you'll have your count. - John www.JohnMichl.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
=SUMPRODUCT(--(C2:C200="text"),--(F2:F200=--"2005-01-01"),--(F2:F200<=--"20
05-01-31")) -- HTH RP (remove nothere from the email address if mailing direct) "MAC" wrote in message ... Col C = Text and Col F = dates I would like to count the # of times a value occurs in Col C based on a date range in Col F. Does anybody have an answer to this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
Hey John, see Bob's solution below - you can reference the entire column.
MAC "John Michl" wrote: Sounds like a job for SUMPRODUCT but note you cannot reference the entire column using this function. Assume that A1 is the date you are using as criteria and A2 is the value you are trying to find in Col F. =SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2))) How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE values where the "--" turns them into 1 for TRUE and 0 for FALSE. F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each pair of 1's and 0's then adds them up. So if the first pair (C1 and F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the second pair are both TRUE the result would be 1 x 1 or 1. Add up all of the ones and you'll have your count. - John www.JohnMichl.com |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
That did it! Thanks Bob!
"Bob Phillips" wrote: =SUMPRODUCT(--(C2:C200="text"),--(F2:F200=--"2005-01-01"),--(F2:F200<=--"20 05-01-31")) -- HTH RP (remove nothere from the email address if mailing direct) "MAC" wrote in message ... Col C = Text and Col F = dates I would like to count the # of times a value occurs in Col C based on a date range in Col F. Does anybody have an answer to this? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
Bob is not referencing the entire column he is referencing the range
from row 2 through row 200. The entire column would be represented by $C:$C. This is not permitted in SUMPRODUCT. - John |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
I am doing something similar. I have a column (AX) full of several different
texts. These texts are OPEN, CLOSED, WORKING ... I have another column with dates (B). I want to have all the data on the 1st worksheet. I want to have 4 subsequent worksheets, one for each quarter of the year. I want worksheet 2 to give me a total of open cases between 1-1-2005 and 3-31-2005. Then another for total closed and total working....etc. I tried to use this formula, but it returns a value of zero..... =SUMPRODUCT(--(AX2:AX61="OPEN"),--(B2:B61=--"2005-10-01"),--(B2:B61<=--"2005-12-31")) Do I have something messed uP? "MAC" wrote: That did it! Thanks Bob! "Bob Phillips" wrote: =SUMPRODUCT(--(C2:C200="text"),--(F2:F200=--"2005-01-01"),--(F2:F200<=--"20 05-01-31")) -- HTH RP (remove nothere from the email address if mailing direct) "MAC" wrote in message ... Col C = Text and Col F = dates I would like to count the # of times a value occurs in Col C based on a date range in Col F. Does anybody have an answer to this? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF date falls within a certain month
I have a similar problem but I am not familiar with SUMPRODUCT.
I have a range of dates and I want to get a count of cells by month. (How many January, February, etc.) So, if A1:A5 is 1/2/2006 2/2/2006 3/2/2006 3/5/2006 4/2/2006 ....and if I'm looking for the number of dates in March I want to... COUNTIF(A1:A5, = "3/1/2006" AND < "4/1/2006") (...but of course this formula doesn't work.) So, how would I do it? Thanks, Scott "John Michl" wrote in message oups.com... Sounds like a job for SUMPRODUCT but note you cannot reference the entire column using this function. Assume that A1 is the date you are using as criteria and A2 is the value you are trying to find in Col F. =SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2))) How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE values where the "--" turns them into 1 for TRUE and 0 for FALSE. F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each pair of 1's and 0's then adds them up. So if the first pair (C1 and F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the second pair are both TRUE the result would be 1 x 1 or 1. Add up all of the ones and you'll have your count. - John www.JohnMichl.com |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF date falls within a certain month
=countif(a1:a5,"="&date(2006,3,1)) - countif(a1:a5,"="&date(2006,4,1))
is one way. Scott Lolmaugh wrote: I have a similar problem but I am not familiar with SUMPRODUCT. I have a range of dates and I want to get a count of cells by month. (How many January, February, etc.) So, if A1:A5 is 1/2/2006 2/2/2006 3/2/2006 3/5/2006 4/2/2006 ...and if I'm looking for the number of dates in March I want to... COUNTIF(A1:A5, = "3/1/2006" AND < "4/1/2006") (...but of course this formula doesn't work.) So, how would I do it? Thanks, Scott "John Michl" wrote in message oups.com... Sounds like a job for SUMPRODUCT but note you cannot reference the entire column using this function. Assume that A1 is the date you are using as criteria and A2 is the value you are trying to find in Col F. =SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2))) How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE values where the "--" turns them into 1 for TRUE and 0 for FALSE. F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each pair of 1's and 0's then adds them up. So if the first pair (C1 and F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the second pair are both TRUE the result would be 1 x 1 or 1. Add up all of the ones and you'll have your count. - John www.JohnMichl.com -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
Jeremy, =SUMPRODUCT(--(AX2:AX61="OPEN"),--(B2:B61=DATE(2005,10,1)),--(B2:B61<=DATE(2005,12,31))) or put your reference dates in two other cells say F1 & G1 =SUMPRODUCT(--(AX1:AX20="OPEN"),--(B1:B20=F1),--(B1:B20<=G1)) or don't use the "--" at all =SUMPRODUCT((AX1:AX20="OPEN")*(B1:B20=F1)*(B1:B20 <=G1)) You were putting the "--" after the = sign in your conditions, this is not necessary. Also, having the dates in " " doesn't seem to work either. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=491906 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
Jeremy, Sorry, forgot to change the ranges. =SUMPRODUCT((AX2:AX61="OPEN")*(B2:B61=F1)*(B2:B61 <=G1)) Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=491906 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF date falls within a certain month
Perfect! Works like a charm.
Thanks!! Scott "Dave Peterson" wrote in message ... =countif(a1:a5,"="&date(2006,3,1)) - countif(a1:a5,"="&date(2006,4,1)) is one way. Scott Lolmaugh wrote: I have a similar problem but I am not familiar with SUMPRODUCT. I have a range of dates and I want to get a count of cells by month. (How many January, February, etc.) So, if A1:A5 is 1/2/2006 2/2/2006 3/2/2006 3/5/2006 4/2/2006 ...and if I'm looking for the number of dates in March I want to... COUNTIF(A1:A5, = "3/1/2006" AND < "4/1/2006") (...but of course this formula doesn't work.) So, how would I do it? Thanks, Scott "John Michl" wrote in message oups.com... Sounds like a job for SUMPRODUCT but note you cannot reference the entire column using this function. Assume that A1 is the date you are using as criteria and A2 is the value you are trying to find in Col F. =SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2))) How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE values where the "--" turns them into 1 for TRUE and 0 for FALSE. F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each pair of 1's and 0's then adds them up. So if the first pair (C1 and F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the second pair are both TRUE the result would be 1 x 1 or 1. Add up all of the ones and you'll have your count. - John www.JohnMichl.com -- Dave Peterson |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
Hi,
I see that it is an old post, however I am in a similar situation. The formula you provided below doesn't reference the entire column. I am referencing data from another worksheet in an entire column. Can you help? Tommy "Bob Phillips" wrote: =SUMPRODUCT(--(C2:C200="text"),--(F2:F200=--"2005-01-01"),--(F2:F200<=--"20 05-01-31")) -- HTH RP (remove nothere from the email address if mailing direct) "MAC" wrote in message ... Col C = Text and Col F = dates I would like to count the # of times a value occurs in Col C based on a date range in Col F. Does anybody have an answer to this? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
You can't reference the whole column if you use Excel up to and included
2003, you would need to use A5:A65535 having said that, do you really have that many rows? If you do formula like that might slow down the workbook quite a bit -- Regards, Peo Sjoblom "Tommy" wrote in message ... Hi, I see that it is an old post, however I am in a similar situation. The formula you provided below doesn't reference the entire column. I am referencing data from another worksheet in an entire column. Can you help? Tommy "Bob Phillips" wrote: =SUMPRODUCT(--(C2:C200="text"),--(F2:F200=--"2005-01-01"),--(F2:F200<=--"20 05-01-31")) -- HTH RP (remove nothere from the email address if mailing direct) "MAC" wrote in message ... Col C = Text and Col F = dates I would like to count the # of times a value occurs in Col C based on a date range in Col F. Does anybody have an answer to this? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF: 2 criteria: Date Range Column & Text Column
Thanks Peo! That really help without referencing the whole column. Well,
the project I am working on continuosly add data to the rows and I thought it would be easier to reference the column. So I set the max to 10000 rows. I was also confused by reading some of the other discussions and wasn't aware that they were talking about 2007 or not. I didn't know that couldn't be done in 2003 and prior. Thanks again! Tommy "Peo Sjoblom" wrote: You can't reference the whole column if you use Excel up to and included 2003, you would need to use A5:A65535 having said that, do you really have that many rows? If you do formula like that might slow down the workbook quite a bit -- Regards, Peo Sjoblom "Tommy" wrote in message ... Hi, I see that it is an old post, however I am in a similar situation. The formula you provided below doesn't reference the entire column. I am referencing data from another worksheet in an entire column. Can you help? Tommy "Bob Phillips" wrote: =SUMPRODUCT(--(C2:C200="text"),--(F2:F200=--"2005-01-01"),--(F2:F200<=--"20 05-01-31")) -- HTH RP (remove nothere from the email address if mailing direct) "MAC" wrote in message ... Col C = Text and Col F = dates I would like to count the # of times a value occurs in Col C based on a date range in Col F. Does anybody have an answer to this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Value between 2 dates | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
I need a formula to find rows within a date range in one column? | Excel Worksheet Functions | |||
use a date range as criteria in a countif formula | Excel Worksheet Functions |