• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to calculate percentages representing time invested by employee to different customers

B

becha69

New Member
here is the sample file. i edit within the description of problem and the potential solution kindly requested
basically the formulas should be able to come up with decimal values for each combo of employee and customer
the trick is that formulas would only rely on the ocurrences or repetitions of employee names along a catalog of different customer labels, to calculate such percentage

please help guys, i really need to have some solution in order to crunch a report tonight
Much appreciation to each of you helping us strangers to reduce our stress work level!

feel free to ask any question. i apologize because my english is not good. hopefully i was able to convey the problem and the expectation of solution
 

Attachments

  • sample for how to pull percentages of support by employee and by customer.xls
    53.5 KB · Views: 7
becha69
Did You show Your expected results somewhere?
If so, then I got some same.
 

Attachments

  • sample for how to pull percentages of support by employee and by customer.xls
    56.5 KB · Views: 8
hello kind person
unfortunately the results are not the expected ones shown manuall in the range of q4:v24
do you have a chance to revised the formulas you porposed? Please...?
 
Here is another formula solution approach, in without helper columns way.

However, I got the same result as same as #2 Vletm's answer. And I suspected you have made adjusting to your forum submission file.

I confirmed that both of Vletm and mine answers are corrected, based on your forum submission file.

Please review of the forum submission file and your actual file.

I also notice that your attached file is xls file which is used in Excel 97-2003, and the COUNTIFS function which we used was introduced in 2007.

So if your Excel version so old, the formula will not be supported by Excel new version.

My single formula in C4, copied across right and all filled down :

=IFERROR(COUNTIFS('employees assigned to customer'!$B:$B,$B4,'employees assigned to customer'!$D:$D,C$3)/SUMPRODUCT(COUNTIFS('employees assigned to customer'!$B:$B,$B4,'employees assigned to customer'!$D:$D,$C$3:$H$3)),0)

79128
 

Attachments

  • sample for how to pull percentages of support by employee and by customer (BY).xls
    76.5 KB · Views: 2
Last edited:
becha69
How have You gotten Your expected results?
Can You give Your logic?
... especially with those Your results, which are different than mine & bosco_yip's.
 
You must log in or register to reply here.
Back
Top