I'm having a heckuva time figuring out a formula. There's gotta be an easy
way to do this. Here's my data:

==================================================
A B C D

1 NUMBER DATE DESCRIPTION CODE
2 1024591 01/01/2006 Description BOL
3 9365846 01/03/2006 Description TXT
4 3784555 01/11/2006 Description WAX
5 3785644 01/20/2006 Description TXT
6 9354651 01/29/2006 Description FAR
7 2057436 02/01/2006 Description TXT
.. ...
.. ...
.. ...
62000 8307532 09/17/2007 Description TXT
==================================================

I figured out how to count the number of entries based on a specified date
range:

=COUNTIF(A2:A62000,">=01/01/2006")-COUNTIF(A2:A62000,">01/31/2006")

And how to count the number of entries based on a specified code:

=COUNTIF(D262000,"=TXT")

Now I need to figure out how to count the number of entries based on a
specified date range and then if the row falls within the specified date
range, to count the number of entries based on a specified code. For example,
using the data above, I would need to say that during January 2006, there
were 5 total entries. Of those 5 entries, 2 were "TXT" codes, 1 was a "BOL"
code, 1 was a "WAX" code, and 1 was a "FAR" code. I hope that makes sense.

Can anyone help me out with this?

Thanx.

=SUMPRODUCT(--(Year(B2:B6200)=2006),--(MONTH(B2:B6200)=1),--(D26200="TXT"))
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

Another way would be to make a Pivot Table; see
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html <---
start here
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2...le-parameters/

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Steven Sinclair" <(E-Mail Removed)> wrote in
message news:A9FB513C-D64B-4DED-BAB7-(E-Mail Removed)...
> I'm having a heckuva time figuring out a formula. There's gotta be an easy
> way to do this. Here's my data:
>
> ==================================================
> A B C D
>
> 1 NUMBER DATE DESCRIPTION CODE
> 2 1024591 01/01/2006 Description BOL
> 3 9365846 01/03/2006 Description TXT
> 4 3784555 01/11/2006 Description WAX
> 5 3785644 01/20/2006 Description TXT
> 6 9354651 01/29/2006 Description FAR
> 7 2057436 02/01/2006 Description TXT
> . ...
> . ...
> . ...
> 62000 8307532 09/17/2007 Description TXT
> ==================================================
>
> I figured out how to count the number of entries based on a specified date
> range:
>
> =COUNTIF(A2:A62000,">=01/01/2006")-COUNTIF(A2:A62000,">01/31/2006")
>
> And how to count the number of entries based on a specified code:
>
> =COUNTIF(D262000,"=TXT")
>
> Now I need to figure out how to count the number of entries based on a
> specified date range and then if the row falls within the specified date
> range, to count the number of entries based on a specified code. For
> example,
> using the data above, I would need to say that during January 2006, there
> were 5 total entries. Of those 5 entries, 2 were "TXT" codes, 1 was a
> "BOL"
> code, 1 was a "WAX" code, and 1 was a "FAR" code. I hope that makes sense.
>
> Can anyone help me out with this?
>
> Thanx.

On Tue, 18 Sep 2007 11:08:01 -0700, Steven Sinclair
<(E-Mail Removed)> wrote:

>I'm having a heckuva time figuring out a formula. There's gotta be an easy
>way to do this. Here's my data:
>
>==================================================
> A B C D
>
>1 NUMBER DATE DESCRIPTION CODE
>2 1024591 01/01/2006 Description BOL
>3 9365846 01/03/2006 Description TXT
>4 3784555 01/11/2006 Description WAX
>5 3785644 01/20/2006 Description TXT
>6 9354651 01/29/2006 Description FAR
>7 2057436 02/01/2006 Description TXT
>. ...
>. ...
>. ...
>62000 8307532 09/17/2007 Description TXT
>==================================================
>
>I figured out how to count the number of entries based on a specified date
>range:
>
>=COUNTIF(A2:A62000,">=01/01/2006")-COUNTIF(A2:A62000,">01/31/2006")
>
>And how to count the number of entries based on a specified code:
>
>=COUNTIF(D262000,"=TXT")
>
>Now I need to figure out how to count the number of entries based on a
>specified date range and then if the row falls within the specified date
>range, to count the number of entries based on a specified code. For example,
>using the data above, I would need to say that during January 2006, there
>were 5 total entries. Of those 5 entries, 2 were "TXT" codes, 1 was a "BOL"
>code, 1 was a "WAX" code, and 1 was a "FAR" code. I hope that makes sense.
>
>Can anyone help me out with this?
>
>Thanx.

Have you tried a Pivot Table (Under the Data menu)?

Drag the Date to the row area; the Code to the column area, and the Code again
to the data area.

Then group the Dates by months.

You can get a result similar to below:


DATE BOL FAR TXT WAX Grand Total
Jan 1 1 2 1 5
Feb 1 1


--ron