Help with this Formula

[Cosmop001]

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.

Danelle	Email	W14120185DVT	19/08/2017	19/10/2016	19/10/2016	Sit down
Danelle	Email	W14120186DVT	29/04/2017	19/10/2016	20/10/2016	Sit down
Kreena	Email	C14120187KP	Declined	24/11/2016	19/10/2016		Canapés
Danelle	Phone	C14120188DVT	12/11/2016	20/10/2016	20/10/2016	Other
Danelle	Email	P14120189DVT		25/11/2016	20/10/2016	20/10/2016	Canapés

<tbody>
[TD="bgcolor: #FFF2CC"]Assigned to[/TD]
[TD="bgcolor: #FFF2CC"]QuoteSource(Tel,Email,Repeat, Recom, CC)[/TD]
[TD="bgcolor: #FFF2CC"]PBCode[/TD]
[TD="bgcolor: #FFF2CC, align: right"]Status[/TD]
[TD="bgcolor: #D9EAD3, align: right"]EventDate[/TD]
[TD="bgcolor: #D9EAD3, align: right"]Date Quote In[/TD]
[TD="bgcolor: #D9EAD3, align: right"]DateQuoteSent[/TD]
[TD="bgcolor: #D9EAD3"]Type of Function[/TD]
[TD="bgcolor: #D9EAD3, align: right"]No# Guests[/TD]
[TD="bgcolor: #D9EAD3, align: right"]Estimate (ex Vat)[/TD]
[TD="bgcolor: #D9EAD3, align: right"]Total (Inc VAT)[/TD]

[TD="bgcolor: #9FC5E8"]Quote sent[/TD]

[TD="align: right"]120[/TD]
[TD="align: right"]£139,105.00[/TD]
[TD="align: right"]£166,926.00[/TD]

[TD="bgcolor: #9FC5E8"]Quote sent[/TD]

[TD="align: right"]85[/TD]
[TD="align: right"]£6,705.00[/TD]
[TD="align: right"]£8,046.00[/TD]

[TD="align: right"]60[/TD]

[TD="align: right"]£0.00[/TD]

[TD="bgcolor: #9FC5E8"]Quote sent[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]£176.00[/TD]
[TD="align: right"]£211.20[/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]£0.00[/TD]

</tbody>

Next work sheet

I want

[TABLE="width: 814"]
<tbody>[TR]
[TD][/TD]
[TD]Date Range[/TD]
[TD][/TD]
[TD="align: right"]01/10/2016[/TD]
[TD]01/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Accepted[/TD]
[TD]Pending[/TD]
[TD]Quote Sent[/TD]
[TD]Declined[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chelsea[/TD]
[TD]£89.00[/TD]
[TD]£25.00[/TD]
[TD]£30.00[/TD]
[TD]£20.00[/TD]
[TD]£164.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]%[/TD]
[TD]54%[/TD]
[TD]15%[/TD]
[TD]18%[/TD]
[TD]12%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Danelle[/TD]
[TD]£0.00[/TD]
[TD]£13,900.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£13,900.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kreena[/TD]
[TD]£0.00[/TD]
[TD]£1.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£1.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Charlotte[/TD]
[TD]£0.00[/TD]
[TD]£1.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£1.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Anyway help would be appreciated

Thanks

Cosmop

 

[jorismoerings]

Hi and welcome to MrExcel,

try this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1		Date Range		1-10-2016	1-1-2017
2	Accepted	Pending	Quote Sent	Declined	Total
3	Chelsea	£ -	£ -	£ -	£ -
4	%	0%	0%	0%	0%
5	Danelle	£ -	£ 175.183,20	£ -	£ 175.183,20
6	%	0%	100%	0%	100%
7	Kreena	£ -	£ -	£ -	£ -
8	%	0%	0%	0%	0%
9	Charlotte	£ -	£ -	£ -	£ -
10	%	0%	0%	0%	0%
Sheet2

Cell Formulas
Range	Formula
B5	=SUMPRODUCT((Sheet1!$A$2:$A$6=Sheet2!$A5)*(Sheet1!$D$2:$D$6="")*((Sheet1!$F$2:$F$6>=Sheet2!$D$1)*(Sheet1!$F$2:$F$6<=Sheet2!$E$1))*(Sheet1!$K$2:$K$6))
B6	=IFERROR(B5/$E5,0)
C5	=SUMPRODUCT((Sheet1!$A$2:$A$6=Sheet2!$A5)*(Sheet1!$D$2:$D$6=C2)*((Sheet1!$F$2:$F$6>=Sheet2!$D$1)*(Sheet1!$F$2:$F$6<=Sheet2!$E$1)),Sheet1!$K$2:$K$6)
C6	=IFERROR(C5/$E5,0)
D5	=SUMPRODUCT((Sheet1!$A$2:$A$6=Sheet2!$A5)*(Sheet1!$D$2:$D$6=D2)*(Sheet1!$F$2:$F$6>=Sheet2!$D$1)*(Sheet1!$F$2:$F$6<=Sheet2!$E$1),Sheet1!$K$2:$K$6)
D6	=IFERROR(D5/$E5,0)
E5	=SUM(B5:D5)
E6	=SUM(B6:D6)

 

[Cosmop001]

Perfect, thanks very much. Reads like a book!