Compound Criteria for CountIf
 

Excel 2003

I have a worksheet with the following data in cells B7:C12.

Dept. Date
DOC 11/13/2008
DOT 12/1/2008
AGR 12/12/2008
AGR 12/23/2008
AGR 1/10/2009
DOJ 2/10/2009

I need a way to count all the occurrences for a given department name that
fall into a given date range. For example, I need to count all the entries
for "AGR" in the month of December. Using COUNTIF() I can count all
occurrences of "AGR", but I can't figure out how to get just the ones in the
month of December.

Currently, the file is very small, but it may well grow to be thousands of
lines, so I need a solution that will perform well for a large amount of
data.

Any help that you can offer will be greatly appreciated.

--Tom

Compound Criteria for CountIf
 

Try this:

=SUMPRODUCT((B7:B2000="AGR")*(TEXT(C7:C2000,"MMM-YY")="Dec-08"))

Adjust the ranges to suit, but you can't use full-column references in
Excel 2003.

Hope this helps.

Pete

On Nov 25, 1:31*am, "Thomas M." wrote:
Excel 2003

I have a worksheet with the following data in cells B7:C12.

Dept. * Date
DOC * 11/13/2008
DOT * 12/1/2008
AGR * 12/12/2008
AGR * 12/23/2008
AGR * 1/10/2009
DOJ * *2/10/2009

I need a way to count all the occurrences for a given department name that
fall into a given date range. *For example, I need to count all the entries
for "AGR" in the month of December. *Using COUNTIF() I can count all
occurrences of "AGR", but I can't figure out how to get just the ones in the
month of December.

Currently, the file is very small, but it may well grow to be thousands of
lines, so I need a solution that will perform well for a large amount of
data.

Any help that you can offer will be greatly appreciated.

--Tom

Compound Criteria for CountIf
 

Hi,

You can do this with a formula or a pivot table. Here is a formula approach:

=SUMPRODUCT(--(B7:B12="AGR"),--(MONTH(C7:C12)=12))

If you enter the type, AGR in cell A1 and the month number 12, in A2 the
formula would be

=SUMPRODUCT(--(B7:B12=A1),--(MONTH(C7:C12)=A2))

If this helps, please click the Yes button

Cheers,
Shane Devenshire
"Thomas M." wrote:

Excel 2003

I have a worksheet with the following data in cells B7:C12.

Dept. Date
DOC 11/13/2008
DOT 12/1/2008
AGR 12/12/2008
AGR 12/23/2008
AGR 1/10/2009
DOJ 2/10/2009

I need a way to count all the occurrences for a given department name that
fall into a given date range. For example, I need to count all the entries
for "AGR" in the month of December. Using COUNTIF() I can count all
occurrences of "AGR", but I can't figure out how to get just the ones in the
month of December.

Currently, the file is very small, but it may well grow to be thousands of
lines, so I need a solution that will perform well for a large amount of
data.

Any help that you can offer will be greatly appreciated.

--Tom

Compound Criteria for CountIf
 

Worked perfectly. Thanks!

--Tom

"Pete_UK" wrote in message
...
Try this:

=SUMPRODUCT((B7:B2000="AGR")*(TEXT(C7:C2000,"MMM-YY")="Dec-08"))

Adjust the ranges to suit, but you can't use full-column references in
Excel 2003.

Hope this helps.

Pete

On Nov 25, 1:31 am, "Thomas M." wrote:
Excel 2003

I have a worksheet with the following data in cells B7:C12.

Dept. Date
DOC 11/13/2008
DOT 12/1/2008
AGR 12/12/2008
AGR 12/23/2008
AGR 1/10/2009
DOJ 2/10/2009

I need a way to count all the occurrences for a given department name that
fall into a given date range. For example, I need to count all the entries
for "AGR" in the month of December. Using COUNTIF() I can count all
occurrences of "AGR", but I can't figure out how to get just the ones in
the
month of December.

Currently, the file is very small, but it may well grow to be thousands of
lines, so I need a solution that will perform well for a large amount of
data.

Any help that you can offer will be greatly appreciated.

--Tom

Compound Criteria for CountIf
 

Thanks. I haven't had a chance to try the pivot table suggestion yet, but I
will in the next few days because I need to learn how to do pivot tables
anyway.

--Tom

"Shane Devenshire" wrote in
message ...
Hi,

You can do this with a formula or a pivot table. Here is a formula
approach:

=SUMPRODUCT(--(B7:B12="AGR"),--(MONTH(C7:C12)=12))

If you enter the type, AGR in cell A1 and the month number 12, in A2 the
formula would be

=SUMPRODUCT(--(B7:B12=A1),--(MONTH(C7:C12)=A2))

If this helps, please click the Yes button

Cheers,
Shane Devenshire
"Thomas M." wrote:

Excel 2003

I have a worksheet with the following data in cells B7:C12.

Dept. Date
DOC 11/13/2008
DOT 12/1/2008
AGR 12/12/2008
AGR 12/23/2008
AGR 1/10/2009
DOJ 2/10/2009

I need a way to count all the occurrences for a given department name
that
fall into a given date range. For example, I need to count all the
entries
for "AGR" in the month of December. Using COUNTIF() I can count all
occurrences of "AGR", but I can't figure out how to get just the ones in
the
month of December.

Currently, the file is very small, but it may well grow to be thousands
of
lines, so I need a solution that will perform well for a large amount of
data.

Any help that you can offer will be greatly appreciated.

--Tom

Compound Criteria for CountIf
 

You're welcome, Tom - thanks for feeding back.

Pete

On Nov 26, 9:56*pm, "Thomas M." wrote:
Worked perfectly. *Thanks!

--Tom