Use multiple criteria with COUNTIF: between dates and not blank

Using Excel 2003 I need to count rows where the date in column A is in a
particular month and year and column G is not blank.

=SUMPRODUCT((YEAR(A1:A999)=2006)*(MONTH(A1:A999)=6)*(G1:G999<>""))

HTH
--
AP

"l.shields" <l.shields@discussions.microsoft.com> a écrit dans le message de
news: 31C32C19-9C42-4F23-AEBE-93676CAB7128@microsoft.com...
> Using Excel 2003 I need to count rows where the date in column A is in a
> particular month and year and column G is not blank.

=SUMPRODUCT(--(G2:G20<>""),--(MONTH(A2:A20)=1))

for January

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"l.shields" <l.shields@discussions.microsoft.com> wrote in message
news:31C32C19-9C42-4F23-AEBE-93676CAB7128@microsoft.com...
> Using Excel 2003 I need to count rows where the date in column A is in a
> particular month and year and column G is not blank.

oops, mis-read it. I see that now seeing Ardus' response.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:%23eQQr34kGHA.4212@TK2MSFTNGP03.phx.gbl...
> =SUMPRODUCT(--(G2:G20<>""),--(MONTH(A2:A20)=1))
>
> for January
>
> Note that SUMPRODUCT doesn't work with complete columns, you have to
specify
> a range.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "l.shields" <l.shields@discussions.microsoft.com> wrote in message
> news:31C32C19-9C42-4F23-AEBE-93676CAB7128@microsoft.com...
> > Using Excel 2003 I need to count rows where the date in column A is in a
> > particular month and year and column G is not blank.
>
>

You're the fastest shooter West of Colorado, Bob!

Cheers,
--
AP

"Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
uOcfDX5kGHA.3816@TK2MSFTNGP02.phx.gbl...
> oops, mis-read it. I see that now seeing Ardus' response.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:%23eQQr34kGHA.4212@TK2MSFTNGP03.phx.gbl...
>> =SUMPRODUCT(--(G2:G20<>""),--(MONTH(A2:A20)=1))
>>
>> for January
>>
>> Note that SUMPRODUCT doesn't work with complete columns, you have to
> specify
>> a range.
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "l.shields" <l.shields@discussions.microsoft.com> wrote in message
>> news:31C32C19-9C42-4F23-AEBE-93676CAB7128@microsoft.com...
>> > Using Excel 2003 I need to count rows where the date in column A is in
>> > a
>> > particular month and year and column G is not blank.
>>
>>
>
>

I'm actually East of Colorado!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:Oi8jEj5kGHA.1936@TK2MSFTNGP04.phx.gbl...
> You're the fastest shooter West of Colorado, Bob!
>
> Cheers,
> --
> AP
>
> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
> uOcfDX5kGHA.3816@TK2MSFTNGP02.phx.gbl...
> > oops, mis-read it. I see that now seeing Ardus' response.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> > news:%23eQQr34kGHA.4212@TK2MSFTNGP03.phx.gbl...
> >> =SUMPRODUCT(--(G2:G20<>""),--(MONTH(A2:A20)=1))
> >>
> >> for January
> >>
> >> Note that SUMPRODUCT doesn't work with complete columns, you have to
> > specify
> >> a range.
> >>
> >> --
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> (replace somewhere in email address with gmail if mailing direct)
> >>
> >> "l.shields" <l.shields@discussions.microsoft.com> wrote in message
> >> news:31C32C19-9C42-4F23-AEBE-93676CAB7128@microsoft.com...
> >> > Using Excel 2003 I need to count rows where the date in column A is
in
> >> > a
> >> > particular month and year and column G is not blank.
> >>
> >>
> >
> >
>
>

Many thanks guys for the very quick response. Worked perfectly. Sorry it
took me so long to reply, but I wasn't able to verify my e-mail address.


"Ardus Petus" wrote:

> =SUMPRODUCT((YEAR(A1:A999)=2006)*(MONTH(A1:A999)=6)*(G1:G999<>""))
>
> HTH
> --
> AP
>
> "l.shields" <l.shields@discussions.microsoft.com> a écrit dans le message de
> news: 31C32C19-9C42-4F23-AEBE-93676CAB7128@microsoft.com...
> > Using Excel 2003 I need to count rows where the date in column A is in a
> > particular month and year and column G is not blank.
>
>
>

Hello,

I have a similiar problem but I need to count the number of counts in EVERY
bin of a graph seperately. i.e. How many have an (x,y) of (0,0) (0,1)(1,0)
and so on. For the numbers I am talking about I need to do this several
thousand times and copying the formula below doesn't work because it changes
the column it's looking at every time copy to a new cell. Is there another
way that can be easily copied?

Thanks,

Eliza

"Ardus Petus" wrote:

> =SUMPRODUCT((YEAR(A1:A999)=2006)*(MONTH(A1:A999)=6)*(G1:G999<>""))
>
> HTH
> --
> AP
>
> "l.shields" <l.shields@discussions.microsoft.com> a écrit dans le message de
> news: 31C32C19-9C42-4F23-AEBE-93676CAB7128@microsoft.com...
> > Using Excel 2003 I need to count rows where the date in column A is in a
> > particular month and year and column G is not blank.
>
>
>