# Function To Count The Work Done By Employee on Particular assignments

#### Zackefron

##### New Member
Hello Every One I am Trying from several days a Function to make to manage my work. But not able to succeed. If anybody have some time please look and help.

My excel having 32 worksheet 1 Sheet Contains the summary & rest is date wise for whole month. I need to search the whole workbook to count the work done by employee for each assignments. I am unable to compile the data as every sheet has more than 1200 entries. So need a function which call on first sheet and count the work done by each employee.

This is how every sheet have data:

 Delay Jane Cancellation Ravi Delay Jane

<tbody>
</tbody>

On first sheet we have Summary/Output

 Employee Name Delay Cancellation Jane 2 0 Ravi 0 1

<tbody>
</tbody>

Please Note: The function only count from 1 to 31 and other sheet data should not be counted as to get the right results. Or any other you can suggest to get exact result.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

##### MrExcel MVP
In B2 of Summary sheet enter, copy across to C1, and down:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!A:A")=B\$1,INDIRECT("'"&SheetList&"'!B:B")=\$A2)))

SheetList refers to the range which houses the names of the relevant data sheets.

#### Zackefron

##### New Member
Thanks Buddy can i have a VBA code as we have 31 sheets it make the formula too long. Function with be helpful. Which search the workbook and count. If possible for you please help with that.

##### MrExcel MVP
Thanks Buddy can i have a VBA code as we have 31 sheets it make the formula too long. Function with be helpful. Which search the workbook and count. If possible for you please help with that.

Instead, try to understand what is proposed...

In B2 of Summary sheet enter, copy across to C2, and down:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!A:A")=B\$1,INDIRECT("'"&SheetList&"'!B:B")=\$A2)))

1. SheetList refers to the range which houses the names of the relevant data sheets.
2. B1 = Delay and C1 = Cancellation
3. A2 = Jane, A3 = Ravi, etc.

Each sheet in Sheetlist is assumed to consist of 2 columns: A and B. A houses the work done, B the employee.

Good luck.

#### Zackefron

##### New Member
I tried to put in my excel. But not working. Please find the Link below for excel. Data to be picked from Cell G & P from every sheet. Thanks for considering my request helpful if sorted out.

http://www.filedropper.com/daytoday

Last edited:

#### Zackefron

##### New Member
Thanks for the solution really appreciated but if you implement it in VBA fuction it will helpful as excel will be having huge data. Please consider if possible for you.

##### MrExcel MVP
I tried to put in my excel. But not working. Please find the Link below for excel. Data to be picked from Cell G & P from every sheet. Thanks for considering my request helpful if sorted out.

http://www.filedropper.com/daytoday

You must do it yourself...

Instead, try to understand what is proposed...

In B2 of Summary sheet enter, copy across to C2, and down:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!A:A")=B\$1,INDIRECT("'"&SheetList&"'!B:B")=\$A2)))

1. SheetList refers to the range which houses the names of the relevant data sheets.
2. B1 = Delay and C1 = Cancellation
3. A2 = Jane, A3 = Ravi, etc.

Each sheet in Sheetlist is assumed to consist of 2 columns: A and B. A houses the work done, B the employee.

Good luck.

Edit: Replace the equal sign with the comma separator...

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!A:A"),B\$1,INDIRECT("'"&SheetList&"'!B:B"),\$A2)))

See: https://dl.dropboxusercontent.com/u/65698317/Zackefron conditional count across sheets.xlsx for the set up you can copy/adapt if appropriate.

#### Zackefron

##### New Member
Thanks for your help Aladin. I am just noob in excel & VBA my friend told me this site is very helpful which I already see. I am unable to make function because of less knowledge of VBA & excel will try to search google. That's why asking for VBA function help. Thanks to all. And Happy New Year.

Last edited:

Replies
3
Views
453
Replies
4
Views
296
Replies
0
Views
321
Replies
3
Views
1K
Replies
10
Views
3K

1,195,598
Messages
6,010,644
Members
441,558
Latest member
lambierules

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

### Which adblocker are you using?

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