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

thanks steve. unfortunately, your formula returns "#VALUE!"

any idea why this happens?

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 are:
> 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
>
>

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

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