Hi
I am new to the forum and even though i have used excel for years what function to use has got me stumped. I have a spread sheet that every time we get a sales inquiry, the job gets allocated to a member of staff. From a drop down menu you select the staff members name, on the same row it has details of the job, a drop down menu to say whether the job has been accepted declined or Quote Sent and quote amount and the date. So for a simple example A1 has a drop down of Staff names, D1 has the Declined, Accepted or Quote Sent menu and J1 has the Job Value E1 has the date. On a new Sheet i want to separate the staff and their monthly sales into accepted declined or pending and add the totals of the job value.
At first i thought =IF command but i get stuck. My interpretation would be if A1= Bill and D1 =Accepted i need to add up J1 on the new sheet. The same for other staff and other selections in the status column.
<tbody>
</tbody>
Next work sheet
I want
<tbody>
</tbody>
Anyway help would be appreciated
Thanks
Cosmop
I am new to the forum and even though i have used excel for years what function to use has got me stumped. I have a spread sheet that every time we get a sales inquiry, the job gets allocated to a member of staff. From a drop down menu you select the staff members name, on the same row it has details of the job, a drop down menu to say whether the job has been accepted declined or Quote Sent and quote amount and the date. So for a simple example A1 has a drop down of Staff names, D1 has the Declined, Accepted or Quote Sent menu and J1 has the Job Value E1 has the date. On a new Sheet i want to separate the staff and their monthly sales into accepted declined or pending and add the totals of the job value.
At first i thought =IF command but i get stuck. My interpretation would be if A1= Bill and D1 =Accepted i need to add up J1 on the new sheet. The same for other staff and other selections in the status column.
| Assigned to | QuoteSource(Tel,Email,Repeat, Recom, CC) | PBCode | Status | EventDate | Date Quote In | DateQuoteSent | Type of Function | No# Guests | Estimate (ex Vat) | Total (Inc VAT) |
| Danelle | W14120185DVT | Quote sent | 19/08/2017 | 19/10/2016 | 19/10/2016 | Sit down | 120 | £139,105.00 | £166,926.00 | |
| Danelle | W14120186DVT | Quote sent | 29/04/2017 | 19/10/2016 | 20/10/2016 | Sit down | 85 | £6,705.00 | £8,046.00 | |
| Kreena | C14120187KP | Declined | 24/11/2016 | 19/10/2016 | Canapés | 60 | £0.00 | |||
| Danelle | Phone | C14120188DVT | Quote sent | 12/11/2016 | 20/10/2016 | 20/10/2016 | Other | 40 | £176.00 | £211.20 |
| Danelle | P14120189DVT | 25/11/2016 | 20/10/2016 | 20/10/2016 | Canapés | 30 | £0.00 |
<tbody>
</tbody>
Next work sheet
I want
| Date Range | 01/10/2016 | 01/01/2017 | ||||
| Accepted | Pending | Quote Sent | Declined | Total | ||
| Chelsea | £89.00 | £25.00 | £30.00 | £20.00 | £164.00 | |
| % | 54% | 15% | 18% | 12% | 100% | |
| Danelle | £0.00 | £13,900.00 | £0.00 | £0.00 | £13,900.00 | |
| % | 0% | 100% | 0% | 0% | 100% | |
| Kreena | £0.00 | £1.00 | £0.00 | £0.00 | £1.00 | |
| % | 0% | 100% | 0% | 0% | 100% | |
| Charlotte | £0.00 | £1.00 | £0.00 | £0.00 | £1.00 | |
| % | 0% | 100% | 0% | 0% | 100% |
<tbody>
</tbody>
Anyway help would be appreciated
Thanks
Cosmop
