Identifying Manual percentage on a control location

[hiruyg]

Hi Guys
This is probably easy but I’m a dull when it comes to Excel,
I would like to identify the percentage of manually priced and auto priced tickets for those sold in a six month period.
Auto priced tickets are designated by 0 & 2 while 1 & blank represent Manual pricing.

I would like to identify the percentage of manually priced tickets i.e. 1s & blanks together on a control location level on a single column.
Is can anyone help me come up with a certain formula for this?
Below is an example of how I want it to be (I added the last column manually)

Tickets Date SL Control location Manual/Auto Manual percentage
071-7721717525 2-Jan-16 656229 600 2 50
071-9394835684 2-Jan-16 1320991 1320000 1 63.15789474
071-9772465176 2-Jan-16 1320995 1320000 0 63.15789474
071-9772466403 2-Jan-16 1320995 1320000 63.15789474
071-9772466407 2-Jan-16 1320995 1320000 1 63.15789474
071-7740216547 2-Jan-16 966757 600 50
071-9394835835 2-Jan-16 1320080 1320000 2 63.15789474
071-9394835857 2-Jan-16 1320080 1320000 0 63.15789474
071-9394835861 2-Jan-16 1320080 1320000 1 63.15789474
071-9772465938 2-Jan-16 1320081 1320000 1 63.15789474
071-7740216539 2-Jan-16 966757 600 0 50
071-9772466121 2-Jan-16 1320989 1320000 63.15789474
071-9772466123 2-Jan-16 1320989 1320000 2 63.15789474
071-7740216542 2-Jan-16 966757 600 1 50
071-9394835656 2-Jan-16 1320991 1320000 1 63.15789474
071-9394835684 2-Jan-16 1320991 1320000 1 63.15789474
071-9772465176 2-Jan-16 1320995 1320000 0 63.15789474
071-9772466403 2-Jan-16 1320995 1320000 63.15789474
071-9772466407 2-Jan-16 1320995 1320000 2 63.15789474
071-7740216545 2-Jan-16 966757 600 0 50
071-9772466056 2-Jan-16 1321001 1320000 1 63.15789474
071-9772466242 2-Jan-16 1321001 1320000 1 63.15789474
071-9772453824 2-Jan-16 1321007 1320000 0 63.15789474
071-9772453828 2-Jan-16 1321007 1320000 63.15789474
071-7740216543 2-Jan-16 966757 600 1 50

[Richard Buttrey]

Hi,

Would you upload the workbook along with explanations as to how you are calculating the %s. i.e. which totals (cell refs) are you using.
In addition most of the results appear the same. If there is more variability in your actual data be sure to include different permutations.

[hiruyg]

Hi Richard, the attachment tab wasn't working that's why i tried to show it on the body.
I hope you can see it now. The image is a sample of how i want it to be. i have also uploaded the workbook too.
thanks

[JeteMc]

Try pasting this into F2 (or row 2 of whichever column you wish):

=(COUNTIFS(D$2:D$2996,D270,E$2:E$2996,1)+COUNTIFS(D$2:D$2996,D270,E$2:E$2996,""))/COUNTIFS(D$2:D$2996,D270)

Let us know if you have any questions.

[hiruyg]

Hi Jetemac,
This works perfectly. thank you so much.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[hiruyg]

Will do. Thank you again.