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.

Use multiple criteria with COUNTIF: between dates and not blank
 

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

HTH
--
AP

"l.shields" a écrit dans le message de
news: ...
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.

Use multiple criteria with COUNTIF: between dates and 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" wrote in message
...
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.

Use multiple criteria with COUNTIF: between dates and 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" wrote in message
...
=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" wrote in message
...
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.

Use multiple criteria with COUNTIF: between dates and not blank
 

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

Cheers,
--
AP

"Bob Phillips" a écrit dans le message de news:
...
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" wrote in message
...
=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" wrote in message
...
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.

Use multiple criteria with COUNTIF: between dates and not blank
 

I'm actually East of Colorado!

--
HTH

Bob Phillips

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

"Ardus Petus" wrote in message
...
You're the fastest shooter West of Colorado, Bob!

Cheers,
--
AP

"Bob Phillips" a écrit dans le message de news:
...
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" wrote in message
...
=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" wrote in message
...
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.

Use multiple criteria with COUNTIF: between dates and not bla
 

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" a écrit dans le message de
news: ...
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.

Use multiple criteria with COUNTIF: between dates and not bla
 

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" a écrit dans le message de
news: ...
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.

Use multiple criteria with COUNTIF: between dates and not bla
 

I have a similar issue: I have a range of dates that I'm trackun using the
COUNTIF function based on the dates that are 91-180, 181-270, & 271-365, days
old, based on the TODAY date. for the 90-180days, I'm using the following,
but doesn't pan out:
=COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180),
(MASTER!AI5:AI5997,"<="&TODAY()-90)

Any help would be graetly appreciated!!

Keith

Use multiple criteria with COUNTIF: between dates and not bla
 

Try this:

=SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91))

--
Biff
Microsoft Excel MVP


"Nascarfan88" wrote in message
...
I have a similar issue: I have a range of dates that I'm trackun using the
COUNTIF function based on the dates that are 91-180, 181-270, & 271-365,
days
old, based on the TODAY date. for the 90-180days, I'm using the
following,
but doesn't pan out:
=COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180),
(MASTER!AI5:AI5997,"<="&TODAY()-90)

Any help would be graetly appreciated!!

Keith

Use multiple criteria with COUNTIF: between dates and not bla
 

Thanks much...That works great.
My dates are color coded, so how can I use the same formula, but only count
the dates in a specific color? Can this even be done?

Best regards
--
Keith


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91))

--
Biff
Microsoft Excel MVP


"Nascarfan88" wrote in message
...
I have a similar issue: I have a range of dates that I'm trackun using the
COUNTIF function based on the dates that are 91-180, 181-270, & 271-365,
days
old, based on the TODAY date. for the 90-180days, I'm using the
following,
but doesn't pan out:
=COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180),
(MASTER!AI5:AI5997,"<="&TODAY()-90)

Any help would be graetly appreciated!!

Keith

Use multiple criteria with COUNTIF: between dates and not bla
 

It can be done but it's complicated and it might not work the way you think
it should work. I recommend not doing it that way.

See this:

http://www.cpearson.com/Excel/colors.aspx

--
Biff
Microsoft Excel MVP


"Nascarfan88" wrote in message
...
Thanks much...That works great.
My dates are color coded, so how can I use the same formula, but only
count
the dates in a specific color? Can this even be done?

Best regards
--
Keith


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91))

--
Biff
Microsoft Excel MVP


"Nascarfan88" wrote in message
...
I have a similar issue: I have a range of dates that I'm trackun using
the
COUNTIF function based on the dates that are 91-180, 181-270, &
271-365,
days
old, based on the TODAY date. for the 90-180days, I'm using the
following,
but doesn't pan out:
=COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180),
(MASTER!AI5:AI5997,"<="&TODAY()-90)

Any help would be graetly appreciated!!

Keith

Use multiple criteria with COUNTIF: between dates and not bla
 

Wow...I agree...I'll figure something else.

Thanks very much for your help!
--
Keith


"T. Valko" wrote:

It can be done but it's complicated and it might not work the way you think
it should work. I recommend not doing it that way.

See this:

http://www.cpearson.com/Excel/colors.aspx

--
Biff
Microsoft Excel MVP


"Nascarfan88" wrote in message
...
Thanks much...That works great.
My dates are color coded, so how can I use the same formula, but only
count
the dates in a specific color? Can this even be done?

Best regards
--
Keith


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91))

--
Biff
Microsoft Excel MVP


"Nascarfan88" wrote in message
...
I have a similar issue: I have a range of dates that I'm trackun using
the
COUNTIF function based on the dates that are 91-180, 181-270, &
271-365,
days
old, based on the TODAY date. for the 90-180days, I'm using the
following,
but doesn't pan out:
=COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180),
(MASTER!AI5:AI5997,"<="&TODAY()-90)

Any help would be graetly appreciated!!

Keith

Use multiple criteria with COUNTIF: between dates and not bla
 

See if this gives you an idea...

Your dates are color coded for a reason. Use that reason as the logic in a
formula. If they're color coded using conditional formatting, use the same
logic of the conditional formatting rule to write a formula.

--
Biff
Microsoft Excel MVP


"Nascarfan88" wrote in message
...
Wow...I agree...I'll figure something else.

Thanks very much for your help!
--
Keith


"T. Valko" wrote:

It can be done but it's complicated and it might not work the way you
think
it should work. I recommend not doing it that way.

See this:

http://www.cpearson.com/Excel/colors.aspx

--
Biff
Microsoft Excel MVP


"Nascarfan88" wrote in message
...
Thanks much...That works great.
My dates are color coded, so how can I use the same formula, but only
count
the dates in a specific color? Can this even be done?

Best regards
--
Keith


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91))

--
Biff
Microsoft Excel MVP


"Nascarfan88" wrote in message
...
I have a similar issue: I have a range of dates that I'm trackun
using
the
COUNTIF function based on the dates that are 91-180, 181-270, &
271-365,
days
old, based on the TODAY date. for the 90-180days, I'm using the
following,
but doesn't pan out:
=COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180),
(MASTER!AI5:AI5997,"<="&TODAY()-90)

Any help would be graetly appreciated!!

Keith