Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Dates Based on Other Cell Match (Excel 2000)

    I have a spreadsheet that will be used to track work coming into our dept. All work is pre-identified by dept name (column [img]/forums/images/smilies/cool.gif[/img] and doc number (column C). When a job comes in, that date is entered in column F. When a word processor begins work on a job, that start date is entered in column H. When the word processor has finished a job, that date is entered in column I.

    I am attempting to provide summary info with metrics for each dept. I would like to count every date entered in column F for each individual dept name in column B. Results would be displayed by dept in a cell titled, - "Number of Docs Received." I would use the same formula referencing columns H and B to summarize by dept, "Docs In-Progress," and finally using columns I and B, "Docs Completed."

    Can anyone help me count date cells in one column based upon a dept name in another column range?
    In advance, thank you!!!
    Mary

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting Dates Based on Other Cell Match (Excel 2000)

    Something like this? (To save space I didn't exactly use your layout.)
    <table border=1><td></td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center>H</td><tr><td align=center>1</td><td><u>Dept</td><td><u>Date In</td><td><u>Start Date</td><td><u>Complete Date</td><td align=right>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Dates Based on Other Cell Match (Excel 2000)

    Let B1:B0 in sheet DocData house departments:

    {"dept";"D4";"D4";"D1";"D3";"D1";"D2";"D1";"D5";"D 1"};

    C1:C10 doc numbers:

    {"doc#";"doc1";"doc2";"doc3";"doc4";"doc5";"doc6"; "doc7";"doc8";"doc9"};

    F1:F10 dates docs are received:

    {"date in";37622;37623;37624;37625;37626;37627;37628;3762 9;37630} [ Strange numbers are dates and are formattable as such ];

    H1:H10 dates jobs are started:

    {"date started";37629;37632;37629;37632;37633;37636;0;376 35;37639};

    Finally, I1:I10 dates jobs are finished:

    {"date finished";37640;37642;0;37642;37638;0;0;37642;3764 8}.

    Create a unique list of departments in col A in sheet Summary from A1 on:

    {"dept";"D4";"D1";"D3";"D2";"D5"}

    Enter the following labels B11 in sheet Summary:

    {"Docs Received","Docs In-Progress","Docs Completed"}

    In B2 enter:

    =DCOUNT(DocData!$B$1:$F$10,5,$A$1:A2)-SUM($B$1:B1)

    In C2 enter:

    =DCOUNT(DocData!$B$1:$H$10,7,$A$1:A2)-SUM($C$1:C1)

    In D2 enter:

    =DCOUNT(DocData!$B$1:$I$10,8,$A$1:A2)-SUM($D$11)

    Select B22 and copy down as far as needed.

    Note. The foregoing formulas show a recent method of mine that makes formulas with database functions copiable without repeating labels in criteria.

    See the attachment for sample data and formulas.
    Microsoft MVP - Excel

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Dates Based on Other Cell Match (Excel 2000)

    You are my hero! That did it! I truly appreciate your help! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  5. #5
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Dates Based on Other Cell Match (Excel 2000)

    Thank you for responding to my plea for help. I am off to a meeting, but will give your solution a try this afternoon.
    <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •