Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on countif and sumif function with dates and wildcard characters
Hi, I have 2 columns of data in sheet 1 as follows: B C Open 08/30/05 Closed 08/01/05 Closed 08/30/05 Open 08/03/05 Closed 07/01/05 Closed 07/02/05 In sheet 2, I would like to count how many cells a 1. have august as their dates 2. open on the month of august 3. closed on the month of august 4. etc. so far, i'm trying this count if formula out, but it doesn't work. 1. =COUNTIF(Sheet1!$C$1:$C$65000,"08/**/**") 2. =SUM(IF((Sheet1!$B$1:$B$65000="Open"),IF(Sheet1!$C $1:$C$65000="08/**/**",1,0))) 3. =SUM(IF((Sheet1!$B$1:$B$65000="Closed"),IF(Sheet1! $C$1:$C$65000="08/**/**",1,0))) i don't know if it's the wildcard characters, or if it's the format of my date. please help!! thanks!! -- chinita_jill ------------------------------------------------------------------------ chinita_jill's Profile: http://www.excelforum.com/member.php...o&userid=36536 View this thread: http://www.excelforum.com/showthread...hreadid=562912 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on countif and sumif function with dates and wildcard characters
Try SUMPRODUCT, =SUMPRODUCT((A1:A6="Open")*(MONTH(B1:B6)=8)) Change the "Open" to "Closed" to count the closed. If you want to see a different month then change the 8 in the formula to the corresponding month # (Jan=1,Feb=2,Mar=3.....Dec=12). HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=562912 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on countif and sumif function with dates and wildcard ch
Let's say your dates are in rows 2-7 for this example
1. have august as their dates =SUMPRODUCT(--(C$2:C$7=DATE(2005,8,1)),--(C$2:C$7<=DATE(2005,8,30))) 2. open on the month of august =SUMPRODUCT(--(C$2:C$7=DATE(2005,8,1)),--(C$2:C$7<=DATE(2005,8,30)),--(B$2:B$7="Open")) 3. closed on the month of august =SUMPRODUCT(--(C$2:C$7=DATE(2005,8,1)),--(C$2:C$7<=DATE(2005,8,30)),--(B$2:B$7="Closed")) 4. etc. "chinita_jill" wrote: Hi, I have 2 columns of data in sheet 1 as follows: B C Open 08/30/05 Closed 08/01/05 Closed 08/30/05 Open 08/03/05 Closed 07/01/05 Closed 07/02/05 In sheet 2, I would like to count how many cells a 1. have august as their dates 2. open on the month of august 3. closed on the month of august 4. etc. so far, i'm trying this count if formula out, but it doesn't work. 1. =COUNTIF(Sheet1!$C$1:$C$65000,"08/**/**") 2. =SUM(IF((Sheet1!$B$1:$B$65000="Open"),IF(Sheet1!$C $1:$C$65000="08/**/**",1,0))) 3. =SUM(IF((Sheet1!$B$1:$B$65000="Closed"),IF(Sheet1! $C$1:$C$65000="08/**/**",1,0))) i don't know if it's the wildcard characters, or if it's the format of my date. please help!! thanks!! -- chinita_jill ------------------------------------------------------------------------ chinita_jill's Profile: http://www.excelforum.com/member.php...o&userid=36536 View this thread: http://www.excelforum.com/showthread...hreadid=562912 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on countif and sumif function with dates and wildcard characters
thanks steve. unfortunately, your formula returns "#VALUE!" any idea why this happens? -- chinita_jill ------------------------------------------------------------------------ chinita_jill's Profile: http://www.excelforum.com/member.php...o&userid=36536 View this thread: http://www.excelforum.com/showthread...hreadid=562912 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on countif and sumif function with dates and wildcard characters
Did you change the range in the formula to mathc yours? =SUMPRODUCT((B1:B6="Open")*(MONTH(C1:C6)=8)) If you are concerned with the year as well as the month then, =SUMPRODUCT((B1:B6="Open")*(MONTH(C1:C6)=8)*(YEAR( C1:C6)=2005)) HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=562912 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on countif and sumif function with dates and wildcard characters
One other thing, your ranges need to be the same size (B1:B6, C1:C6). If you tried something like B1:B6 and C1:C11, it would return the VALUE error too. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=562912 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|