Counting using multiple criteria
 

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

Counting using multiple criteria
 

=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

Counting using multiple criteria
 

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

Counting using multiple criteria
 

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

Counting using multiple criteria
 

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

Counting using multiple criteria
 

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

Counting using multiple criteria
 

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

Counting using multiple criteria
 

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

Counting using multiple criteria
 

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

Counting using multiple criteria
 

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

Counting using multiple criteria
 

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

Counting using multiple criteria
 

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