Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Counting Problem (Excel 2000)

    I have a rather complex counting problem I need a bit of help with.

    Each month I export from Access to Excel the daily productivity records for each employee for the previous month.

    Currently I

  2. #2
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Counting Problem (Excel 2000)

    Hi Hans,

    I'm actually trying to automate the process as much as possible for other users, who know very little about Access and Excel, which is why I'm going this direction. I'm actually displaying a good deal of the info in a Pivot table, but because of the way it's structured and the other formulas that are calculating data from the Pivot table resutls I can't calculate what I need, as described below. I figure there's probably a way to do this, but I'm still learning how to write more complex formulas.

    Thanks for your help.

    Scott

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Counting Problem (Excel 2000)

    Since you mention that the data are exported from Access, wouldn't it be easier to set up a few group by queries in Access and export those?

    In Excel, a pivot table based on the table exported from Access might be just the ticket to do this.

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

    Re: Complex Counting Problem (Excel 2000)

    Care to attach a downsized sample of your worksheet?
    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Counting Problem (Excel 2000)

    Hi Aladin,

    I slimed the file down to less than 100k. I'm counting the date in worksheet UF_Jan!E1, employees in UF_Jan!E2 and I have a work around formula in UF_Jan!E3, but since it's not dynamic, it would need to be edited as employee staffing by group changes. Ideally, I'd like to have a formulas similar to the ones E1 & E2; one that counts just the number of employees from Group 1 and another that counts just the number of employees in Group 2. I would then reference these cells in current formula in E3.

    Thanks for your help.

    Scott

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Complex Counting Problem (Excel 2000)

    Enter these ARRAY formulas
    For Group 1:
    =SUM(IF((LEN(UF_Data!NAME_UF_JAN)>0)*(UF_Data!GROU P_UF_JAN="Group 1"),1/COUNTIF(UF_Data!NAME_UF_JAN,UF_Data!NAME_UF_JAN)))

    For Group 2
    =SUM(IF((LEN(UF_Data!NAME_UF_JAN)>0)*(UF_Data!GROU P_UF_JAN="Group 2"),1/COUNTIF(UF_Data!NAME_UF_JAN,UF_Data!NAME_UF_JAN)))

    Instead of "Group 1" and "Group 2" "hardcoded in you could reference a cell or a range name.

    Steve

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Counting Problem (Excel 2000)

    Steve-

    You the man! It worked like a champ AND I only needed to use one formula because I referenced the value as a part of my pivot table selection.

    Thanks!

    Scott

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

    Re: Complex Counting Problem (Excel 2000)

    Scott,

    Your UF_Data appears to consist of dates solely in Jan 03. Is this also the case in the larger workbook?

    Aladin
    Microsoft MVP - Excel

  9. #9
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Counting Problem (Excel 2000)

    Hi Aladin,

    Actually Steve provided a solution for my problem that works great, so problem solved!

    But to answer your question, yes, there would be a named range for each month.

    Thanks for your offer to help.

    Scott

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

    Re: Complex Counting Problem (Excel 2000)

    Scott,

    I'm aware of Steve's contribution. I'm afraid those formula's are a bit risky to use for conditional unique count.

    What I was asking is whether you have any other dates (e.g. February dates) in the date column than just January dates. If any date can be there, is the data sorted on the date column?

    Aladin
    Microsoft MVP - Excel

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Counting Problem (Excel 2000)

    Aladin,

    By "risky", do you mean unreliable? It seems to be working fine.

    To your question. Yes there will be data sets for each month. I've attached another to better illustrate.

    Thanks again for your help.

    Scott

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Counting Problem (Excel 2000)

    Could you explain what you mean by risky?
    Legare Coleman

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

    Re: Complex Counting Problem (Excel 2000)

    A bit history

    [1]

    {=SUM(1/COUNTIF(Range,Range))}

    which is due to David Hager.

    [2]

    {=SUM(IF(LEN(Range),1/COUNTIF(Range,Range)))

    LEN addition by me in order to eliminate the problem due to the empty cells or cells housing formulas that return "".

    These array-formulas no doubt the best to compute a unique items count wrt a definite range.

    Conditional unique count is a different matter...

    Let A2:B8 house

    {"a",1;"a","";"",1;"b",2;"c",1;"b",2;"d",3}

    where "" stands for an empty cell or for a formula cell generating "".

    In D2 enter: 1

    In E2 array-enter:

    =SUM(IF((LEN(A2:A8)>0)*(B2:B8=D2),1/COUNTIF(A2:A8,A2:A8)))

    It returns 1.5.
    Microsoft MVP - Excel

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

    Re: Complex Counting Problem (Excel 2000)

    Scott,

    As the attached file shows, UFTable is defined (using Insert|Name|Define) as referring to:

    =UF_Data!$A$2:INDEX(UF_Data!$F:$F,Admin!$B$2)

    B2 in Admin (an additional worksheet) houses the following formula:

    =MATCH(9.99999999999999E+307,UF_Data!A:A)

    Note 1. Although column A houses dates, its underlying format was not numeric. Now it is. The forgoing MATCH formula requires that change.

    Sheet UF_Jan (This sheet also has a summary on Feb: either change the name or move the Feb summary to a UF_Feb sheet)...

    The array-formula in D3...

    =MIN(IF(TEXT(INDEX(UFTable,0,1),"mmmyyyy")=D$2&$D$ 1,ROW(INDEX(UFTable,0,1)),""))

    computes the start row of the Jan 2003 data.

    The array-formula in D4...

    =MAX((TEXT(INDEX(UFTable,0,1),"mmmyyyy")=D$2&$D$1) *ROW(INDEX(UFTable,0,1)))

    computes the end row for the same data.

    Since we need COUNTDIFF for conditional unique count, this function is also used for non-conditional unique counts:

    The ordinary formula in D5...

    =COUNTDIFF(INDEX(UF_Data!$A:$A,D$3,1):INDEX(UF_Dat a!$A:$A,D$4,1))

    counts distict January dates in UF_Data.

    The ordinary formula in D6...

    =COUNTDIFF((INDEX(UF_Data!$B:$B,D$3,1):INDEX(UF_Da ta!$B:$B,D$4,1)))

    counts distinct January employees.

    The array-formula in D7 for conditional unique count...

    =COUNTDIFF(IF((INDEX(UF_Data!$C:$C,D$3,1):INDEX(UF _Data!$B:$B,D$4,1))=$C7,(INDEX(UF_Data!$B:$B,D$3,1 ):INDEX(UF_Data!$B:$B,D$4,1))))-1

    counts distinct January employees in Group 1.

    This formula is copied down for other groups.

    Note 2. In order to use COUNTDIFF, you need to download and install the morefunc.xll add-in from:

    http://longre.free.fr/english/index.html.

    Note 3. The foregoing is set up with dynamic name ranges. If all this is too much hassle or not really needed, you can replace the INDEX bits that specify ranges with definite ranges or with names that refer to definite ranges (like your DATE_UF_JAN, etc.).

    Aladin
    Microsoft MVP - Excel

Posting Permissions

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