Difficult formula...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven Sinclair - 18 Sep 2007 19:08 GMT

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.

Reply to this Message

Bernard Liengme - 18 Sep 2007 19:18 GMT

=SUMPRODUCT(--(Year(B2:B6200)=2006),--(MONTH(B2:B6200)=1),--(D2:D6200="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

Signature

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

> I'm having a heckuva time figuring out a formula. There's gotta be an easy
> way to do this. Here's my data:
[quoted text clipped - 36 lines]
>
> Thanx.

Reply to this Message

Ron Rosenfeld - 18 Sep 2007 19:22 GMT

>I'm having a heckuva time figuring out a formula. There's gotta be an easy
>way to do this. Here's my data:
[quoted text clipped - 34 lines]
>
>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

Reply to this Message