Count unique values from worksheets

JakeTheSnake3.0

Board Regular
Hello,

Sorry this might take a bit to explain, but here goes:

I have a workbook that keeps track of employee payments. Each pay period is accounted for on each subsequent line starting from 6. What I need to do is to figure out how many unique employees were working for a given month (which includes multiple pay periods).

I count the employees by checking the "paid" column for a given pay period. If there's something in there, that means they worked for that given pay period.

Here is the formula I have so far:
Code:
``=COUNTA('Employee1:Employee20'!M6:M7)``

M6 is pay period 1...M7 is pay period 2...Both pay period 1 and 2 fall under "January"; so what I need to do, is find out IF the employee worked in January at all. The problem with this formula, is that it counts BOTH M6 and M7. So that pretty much eliminates uniqueness.

Any thoughts?

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

xld

Banned
What tells you that M6 AND M7 is January?

hkaplan2

Active Member
Sounds more like a sumproduct, but can you post a sample of the table?

xld

Banned
I would put the Employee sheet names in C2, D2, ... etc.

Change the dates in A3 down to last days of month, 31/01, 28/02, ... etc.

=(COUNTIF(INDIRECT(C\$2&"!B:B"),">="&(\$A3-DAY(\$A3)+1))-COUNTIF(INDIRECT(C\$2&"!B:B"),">"&\$A3)>0)

copy that down and across

=SUMPRODUCT(--(C3:Q3))

and copy down

Dave Patton

Well-known Member
Another idea
on each sheet say in S7 create a sum such as =SUM(M6:M7)

=COUNTIF.3D(Start:End!S7,">0") or

=COUNTIF.3D(Employee1:Employee20!S7,">0")

JakeTheSnake3.0

Board Regular
Thanks for the assistance!

Dave Patton

Well-known Member
For clarity and ease of workbook maintenance, put a blank sheet named "Start" in front of first employee's sheet and a blank sheet named "End" after the last employee's sheet.You can hide these 2 sheets.

With the Morefunc Add-in, try =SUMPRODUCT(--(THREED(Start:End!M6)+THREED(Start:End!M7)>0))

Without Morefunc, make a list of relevant sheet names and name it "SheetList" and then try a formula like

=SUMPRODUCT(--(N(INDIRECT("'"&SheetList&"'!M6"))+(N(INDIRECT("'"&SheetList&"'!M7")))>0))

Replies
1
Views
307
Replies
2
Views
515
Replies
17
Views
632
Replies
9
Views
311
Replies
4
Views
264

1,191,191
Messages
5,985,211
Members
439,947
Latest member
fabiannic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back