Function To Count The Work Done By Employee on Particular assignments

Zackefron

New Member
Joined
Jul 9, 2014
Messages
6
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:

DelayJane
CancellationRavi
DelayJane

<tbody>
</tbody>






On first sheet we have Summary/Output

Employee NameDelayCancellation
Jane20
Ravi01

<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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top