Difficult formula...

[Guest]

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.

 

[Bernard Liengme]

=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/2005/06/23/download-pivottable-parameters/

best wishes

 

[Ron Rosenfeld]

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