Difficult formula...


G

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(D2:D62000,"=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.
 
Ad

Advertisements

Ad

Advertisements

R

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(D2:D62000,"=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top