# Thread: Complex Counting Problem (Excel 2000)

1. ## 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. ## 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.

Scott

3. ## 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. ## Re: Complex Counting Problem (Excel 2000)

Care to attach a downsized sample of your worksheet?

5. ## Re: Complex Counting Problem (Excel 2000)

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.

Scott

6. ## 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. ## 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. ## 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?

9. ## Re: Complex Counting Problem (Excel 2000)

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. ## 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?

11. ## Re: Complex Counting Problem (Excel 2000)

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.

Scott

12. ## Re: Complex Counting Problem (Excel 2000)

Could you explain what you mean by risky?

13. ## 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.

14. ## Re: Complex Counting Problem (Excel 2000)

Scott,

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

=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.).