I am using Excel 2003 and I would like to count the number of cells within a
range thar meet multiple criteria. (Note this function is availiable in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

=SUMPRODUCT(--(rng="value1),--(rng=number2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rodman" wrote in message
...
I am using Excel 2003 and I would like to count the number of cells within
a
range thar meet multiple criteria. (Note this function is availiable in
2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

You could use a combination of countifs:-

=COUNTIF(A1:A100,"<100")-COUNTIF(A1:A100,"<75")

All cells in the range a1 - a100 75 <100

Mike

"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells within a
range thar meet multiple criteria. (Note this function is availiable in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

Let me be a bit more specific.

A spreadsheet contains the following data:

A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
..
..
..
Jan 2007 Complete
Jan 2007 Hold

I would like a count (for example): of the number Complete in Jan of 2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col C) is
complete then increment a counter - like ANDing 3 countif functions [i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].

I hope this example bettter expreses the function I am trying to perform.

Thanks In Advance - Again
--
Rodman Veney


"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells within a
range thar meet multiple criteria. (Note this function is availiable in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

See Bob's reply:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete"))

Assumes "Jan" is TEXT not date format

"Rodman" wrote:

Let me be a bit more specific.

A spreadsheet contains the following data:

A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
.
.
.
Jan 2007 Complete
Jan 2007 Hold

I would like a count (for example): of the number Complete in Jan of 2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col C) is
complete then increment a counter - like ANDing 3 countif functions [i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].

I hope this example bettter expreses the function I am trying to perform.

Thanks In Advance - Again
--
Rodman Veney


"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells within a
range thar meet multiple criteria. (Note this function is availiable in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

Thanks for the help/information... It worked like a charm!!! However
(...here comes the rub...) when I try to use the SUMPRODUCT formula using
data that has been extracted from other fields, it does not seem to work.

Example:

A B C D
11 Jan 2006 Jan 2006 Complete
12 Jan 2006 Jan 2006 Complete

Column B was created using the formula:
=IF(LEN(A1)=11, MID(A1,4,3), MID(A1,5,3))

Column C was created using the formula:
=RIGHT(A2,4)

When I use the following SUMPRODUCT formula,
=SUMPRODUCT(--(B1:B2="Jul"),--(C1:C2=2006),--(D1:D2="Complete"))

I get a result of 0, it should be 2.

What am I doing wrong???

Thanks Again
--
Rodman Veney


"Toppers" wrote:

See Bob's reply:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete"))

Assumes "Jan" is TEXT not date format

"Rodman" wrote:

Let me be a bit more specific.

A spreadsheet contains the following data:

A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
.
.
.
Jan 2007 Complete
Jan 2007 Hold

I would like a count (for example): of the number Complete in Jan of 2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col C) is
complete then increment a counter - like ANDing 3 countif functions [i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].

I hope this example bettter expreses the function I am trying to perform.

Thanks In Advance - Again
--
Rodman Veney


"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells within a
range thar meet multiple criteria. (Note this function is availiable in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

Try

=SUMPRODUCT(--(MONTH(A1:A2)=7),--(YEAR(A1:A2)=2006),--(D1:D2="Complete"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rodman" wrote in message
...
Thanks for the help/information... It worked like a charm!!! However
(...here comes the rub...) when I try to use the SUMPRODUCT formula using
data that has been extracted from other fields, it does not seem to work.

Example:

A B C D
11 Jan 2006 Jan 2006 Complete
12 Jan 2006 Jan 2006 Complete

Column B was created using the formula:
=IF(LEN(A1)=11, MID(A1,4,3), MID(A1,5,3))

Column C was created using the formula:
=RIGHT(A2,4)

When I use the following SUMPRODUCT formula,
=SUMPRODUCT(--(B1:B2="Jul"),--(C1:C2=2006),--(D1:D2="Complete"))

I get a result of 0, it should be 2.

What am I doing wrong???

Thanks Again
--
Rodman Veney


"Toppers" wrote:

See Bob's reply:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete"))

Assumes "Jan" is TEXT not date format

"Rodman" wrote:

Let me be a bit more specific.

A spreadsheet contains the following data:

A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
.
.
.
Jan 2007 Complete
Jan 2007 Hold

I would like a count (for example): of the number Complete in Jan of
2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col
C) is
complete then increment a counter - like ANDing 3 countif functions
[i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].

I hope this example bettter expreses the function I am trying to
perform.

Thanks In Advance - Again
--
Rodman Veney


"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells
within a
range thar meet multiple criteria. (Note this function is availiable
in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

try:

=SUMPRODUCT(--(B1:B2="Jul"),--(C1:C2="2006"),--(D1:D2="Complete"))

Column A is treated as TEXT?....how is it formatted?

"Rodman" wrote:

Thanks for the help/information... It worked like a charm!!! However
(...here comes the rub...) when I try to use the SUMPRODUCT formula using
data that has been extracted from other fields, it does not seem to work.

Example:

A B C D
11 Jan 2006 Jan 2006 Complete
12 Jan 2006 Jan 2006 Complete

Column B was created using the formula:
=IF(LEN(A1)=11, MID(A1,4,3), MID(A1,5,3))

Column C was created using the formula:
=RIGHT(A2,4)

When I use the following SUMPRODUCT formula,
=SUMPRODUCT(--(B1:B2="Jul"),--(C1:C2=2006),--(D1:D2="Complete"))

I get a result of 0, it should be 2.

What am I doing wrong???

Thanks Again
--
Rodman Veney


"Toppers" wrote:

See Bob's reply:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete"))

Assumes "Jan" is TEXT not date format

"Rodman" wrote:

Let me be a bit more specific.

A spreadsheet contains the following data:

A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
.
.
.
Jan 2007 Complete
Jan 2007 Hold

I would like a count (for example): of the number Complete in Jan of 2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col C) is
complete then increment a counter - like ANDing 3 countif functions [i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].

I hope this example bettter expreses the function I am trying to perform.

Thanks In Advance - Again
--
Rodman Veney


"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells within a
range thar meet multiple criteria. (Note this function is availiable in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

..... and "Jul" should be "Jan" ?

"Rodman" wrote:

Thanks for the help/information... It worked like a charm!!! However
(...here comes the rub...) when I try to use the SUMPRODUCT formula using
data that has been extracted from other fields, it does not seem to work.

Example:

A B C D
11 Jan 2006 Jan 2006 Complete
12 Jan 2006 Jan 2006 Complete

Column B was created using the formula:
=IF(LEN(A1)=11, MID(A1,4,3), MID(A1,5,3))

Column C was created using the formula:
=RIGHT(A2,4)

When I use the following SUMPRODUCT formula,
=SUMPRODUCT(--(B1:B2="Jul"),--(C1:C2=2006),--(D1:D2="Complete"))

I get a result of 0, it should be 2.

What am I doing wrong???

Thanks Again
--
Rodman Veney


"Toppers" wrote:

See Bob's reply:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete"))

Assumes "Jan" is TEXT not date format

"Rodman" wrote:

Let me be a bit more specific.

A spreadsheet contains the following data:

A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
.
.
.
Jan 2007 Complete
Jan 2007 Hold

I would like a count (for example): of the number Complete in Jan of 2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col C) is
complete then increment a counter - like ANDing 3 countif functions [i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].

I hope this example bettter expreses the function I am trying to perform.

Thanks In Advance - Again
--
Rodman Veney


"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells within a
range thar meet multiple criteria. (Note this function is availiable in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

Thanks for all of you help!

It turns out the date field was formatted as text. When formatted as date
and using the suggestion from Bob Phillips:

=SUMPRODUCT(--(MONTH(A1:A2)=7),--(YEAR(A1:A2)=2006),--(D1:D2="Complete"))

everything worked great!

Thanks again - Kudos to Bob and Toppers!
--
Rodman Veney


"Bob Phillips" wrote:

Try

=SUMPRODUCT(--(MONTH(A1:A2)=7),--(YEAR(A1:A2)=2006),--(D1:D2="Complete"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rodman" wrote in message
...
Thanks for the help/information... It worked like a charm!!! However
(...here comes the rub...) when I try to use the SUMPRODUCT formula using
data that has been extracted from other fields, it does not seem to work.

Example:

A B C D
11 Jan 2006 Jan 2006 Complete
12 Jan 2006 Jan 2006 Complete

Column B was created using the formula:
=IF(LEN(A1)=11, MID(A1,4,3), MID(A1,5,3))

Column C was created using the formula:
=RIGHT(A2,4)

When I use the following SUMPRODUCT formula,
=SUMPRODUCT(--(B1:B2="Jul"),--(C1:C2=2006),--(D1:D2="Complete"))

I get a result of 0, it should be 2.

What am I doing wrong???

Thanks Again
--
Rodman Veney


"Toppers" wrote:

See Bob's reply:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete"))

Assumes "Jan" is TEXT not date format

"Rodman" wrote:

Let me be a bit more specific.

A spreadsheet contains the following data:

A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
.
.
.
Jan 2007 Complete
Jan 2007 Hold

I would like a count (for example): of the number Complete in Jan of
2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col
C) is
complete then increment a counter - like ANDing 3 countif functions
[i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].

I hope this example bettter expreses the function I am trying to
perform.

Thanks In Advance - Again
--
Rodman Veney


"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells
within a
range thar meet multiple criteria. (Note this function is availiable
in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

Fantastic! I was looking for this same solution. While I do NOT understand
how it works, it works.

I had this issue resolve in 5 minutes with Excel 2007, but we use 2003 at
work, so this helps out a lot. Still don't understand how the '--'
works..... :) Thanks.

"Toppers" wrote:

See Bob's reply:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete"))

Assumes "Jan" is TEXT not date format

"Rodman" wrote:

Let me be a bit more specific.

A spreadsheet contains the following data:

A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
.
.
.
Jan 2007 Complete
Jan 2007 Hold

I would like a count (for example): of the number Complete in Jan of 2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col C) is
complete then increment a counter - like ANDing 3 countif functions [i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].

I hope this example bettter expreses the function I am trying to perform.

Thanks In Advance - Again
--
Rodman Veney


"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells within a
range thar meet multiple criteria. (Note this function is availiable in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney

Still don't understand how the '--' works

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Fantastic! I was looking for this same solution. While I do NOT
understand
how it works, it works.

I had this issue resolve in 5 minutes with Excel 2007, but we use 2003 at
work, so this helps out a lot. Still don't understand how the '--'
works..... :) Thanks.

"Toppers" wrote:

See Bob's reply:

=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete"))

Assumes "Jan" is TEXT not date format

"Rodman" wrote:

Let me be a bit more specific.

A spreadsheet contains the following data:

A B C
Jan 2006 Complete
Jan 2006 Complete
Feb 2006 Hold
Dec 2006 In Progress
.
.
.
Jan 2007 Complete
Jan 2007 Hold

I would like a count (for example): of the number Complete in Jan of
2006.
(i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col
C) is
complete then increment a counter - like ANDing 3 countif functions
[i.e.,
countif(a2:a50,"Jan") and countif(b2:b50, "2006") and
countif(c2:c50,"Complete")].

I hope this example bettter expreses the function I am trying to
perform.

Thanks In Advance - Again
--
Rodman Veney


"Rodman" wrote:

I am using Excel 2003 and I would like to count the number of cells
within a
range thar meet multiple criteria. (Note this function is availiable
in 2007
as COUNTIFS).

Can anyone help?
Thanks In Advance
--
Rodman Veney