Use multiple criteria with COUNTIF: between dates and not blank

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

l.shields - 19 Jun 2006 11:29 GMT

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.

Reply to this Message

Ardus Petus - 19 Jun 2006 11:42 GMT

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

HTH
--
AP

> 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.

Reply to this Message

l.shields - 25 Jun 2006 14:00 GMT

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.

> =SUMPRODUCT((YEAR(A1:A999)=2006)*(MONTH(A1:A999)=6)*(G1:G999<>""))
>
[quoted text clipped - 4 lines]
> > 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.

Reply to this Message

Eliza - 13 Jul 2006 19:21 GMT

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

Reply to this Message

Bob Phillips - 19 Jun 2006 11:48 GMT

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

for January

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

Signature

HTH

Bob Phillips

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

> 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.

Reply to this Message

Bob Phillips - 19 Jun 2006 12:44 GMT

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

Signature

HTH

Bob Phillips

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

> =SUMPRODUCT(--(G2:G20<>""),--(MONTH(A2:A20)=1))
>
[quoted text clipped - 5 lines]
> > 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.

Reply to this Message

Ardus Petus - 19 Jun 2006 13:08 GMT

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

Cheers,
--
AP

> oops, mis-read it. I see that now seeing Ardus' response.
>
[quoted text clipped - 9 lines]
>> > a
>> > particular month and year and column G is not blank.

Reply to this Message

Bob Phillips - 19 Jun 2006 14:59 GMT

I'm actually East of Colorado!

Signature

HTH

Bob Phillips

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

> You're the fastest shooter West of Colorado, Bob!
>
[quoted text clipped - 15 lines]
> >> > a
> >> > particular month and year and column G is not blank.

Reply to this Message