Match Multiple Items based on Multiple Criteria

[MRobi]

I'm looking for some help on a sales pipeline report I'm working on.

I have 1 worksheet that our sales team can enter their raw data and edit it as they move through different phases of the sales pipeline from Prospect to Quoted and finally to sold. On another worksheet, I'm trying to pull only the sold data in any given month (I have a drop down I can select for example June 2017, Oct 2017, etc).

My main columns are:
A - Pipeline Phase (Prospect, Quoted, Follow Up, Sold, Sold Split)
B - Date (Various months)
C - Company information
D - Sale Type (New Client, Existing Client, National Client)
E - Dollar Amount

So what I'm trying to do exactly is find a formula that will match Pipeline Phase - Sold, Date - Oct, Sale Type - New Client. One it's returned all that move on to Existing Client and then National Client. Then I need it to move through the 3 sales types for Pipeline Phase - Sold Split.

I can get it to return the data using a helper cell and vlookup, but with that it doesn't return in any sort of order and I end up with a big gap in space between sold and sold split because I have the vlookup formula for sold in the first 50 rows, then sold split in the next 50 rows.

I'm assuming some sort of index/match formula but my knowledge on those is limited.

 

[FDibbins]

Take a look at using SUMIFS() for this...
=SUMIFS(E:E,A:A,"Sold",B:B,"Oct 2017", D:D,"New Client")

This assumes that your dates are not really dates (9/25/2017 etc), but just text showing month and year (Oct 2016 etc)

 

[MRobi]

Take a look at using SUMIFS() for this...
=SUMIFS(E:E,A:A,"Sold",B:B,"Oct 2017", D:D,"New Client")

This assumes that your dates are not really dates (9/25/2017 etc), but just text showing month and year (Oct 2016 etc)

I don't think SUMIFS will do what I'm looking for. And no, the dates are all really dates, but the way the form is setup they're always the 1st day of the month. So 9/1/2017, 10/1/2017, 11/1/2017, etc...

Here's an idea of what the sales guys would fill out

1	A	B	C	D	E
2	Pipeline Phase	Date	Company	Sale Type	Amount
3	Prospect	9/1/2017	Company A	National	$500
4	Quoted	9/1/2017	Company B	New	$450
5	Sold	9/1/2017	Company C	New	$550
6	Following Up	10/1/2017	Company D	Existing	$700
7	Sold Split	10/1/2017	Company E	Existing	$1500
8	Sold	10/1/2017	Company F	New	$750
9	Sold	10/1/2017	Company G	National	$600
10	Sold	11/1/2017	Company H	Existing	$675

<tbody>
</tbody>

Here is what I'm looking to return on another worksheet if I select the month of Oct

Pipeline Phase	Date	Company	Sale Type	Amount
Sold Split	10/1/2017	Company E	Existing	$1500
Sold	10/1/2017	Company G	National	$600
Sold	10/1/2017	Company F	New	$750

<tbody>
</tbody>

 

[pkdeimos]

Hi,

Is the combination of Pipeline Phase, Date, Company, Sale Type unique? If yes, did you consider just auto-filer on the data? Any specific reason that you are looking for a formula to achieve this?

Regards.

 

[FDibbins]

I don't think SUMIFS will do what I'm looking for. And no, the dates are all really dates, but the way the form is setup they're always the 1st day of the month. So 9/1/2017, 10/1/2017, 11/1/2017, etc...

Don't see why it wont work?

	A​	B​	C​	D​	E​
1​	Pipeline Phase	Date	Company	Sale Type	Amount
2​	Prospect	9/1/2017	Company A	National	$500
3​	Quoted	9/1/2017	Company B	New	$450
4​	Sold	9/1/2017	Company C	New	$550
5​	Following Up	10/1/2017	Company D	Existing	$700
6​	Sold Split	10/1/2017	Company E	Existing	$1,500
7​	Sold	10/1/2017	Company F	New	$750
8​	Sold	10/1/2017	Company G	National	$600
9​	Sold	11/1/2017	Company H	Existing	$675

	G​	H​	I​	J​	K​	L​
1​	Pipeline Phase	Date	Company	Sale Type	Amount	sumifs
2​	Sold Split	10/1/2017	Company E	Existing	$1,500	1500​
3​	Sold	10/1/2017	Company G	National	$600	600​
4​	Sold	10/1/2017	Company F	New	$750	750​

L2=SUMIFS(E:E,A:A,G2,B:B,H2,C:C,I2,D:D,J2)
copied down as needed

 

[MRobi]

Don't see why it wont work?

	A​	B​	C​	D​	E​
1​	Pipeline Phase	Date	Company	Sale Type	Amount
2​	Prospect	9/1/2017	Company A	National	$500
3​	Quoted	9/1/2017	Company B	New	$450
4​	Sold	9/1/2017	Company C	New	$550
5​	Following Up	10/1/2017	Company D	Existing	$700
6​	Sold Split	10/1/2017	Company E	Existing	$1,500
7​	Sold	10/1/2017	Company F	New	$750
8​	Sold	10/1/2017	Company G	National	$600
9​	Sold	11/1/2017	Company H	Existing	$675

	G​	H​	I​	J​	K​	L​
1​	Pipeline Phase	Date	Company	Sale Type	Amount	sumifs
2​	Sold Split	10/1/2017	Company E	Existing	$1,500	1500​
3​	Sold	10/1/2017	Company G	National	$600	600​
4​	Sold	10/1/2017	Company F	New	$750	750​

L2=SUMIFS(E:E,A:A,G2,B:B,H2,C:C,I2,D:D,J2)
copied down as needed

That formula will work for what you put in your example, but that example is not at all what I'm looking for it to do.

Im looking for if the pipeline phase is either sold or sold split and the date matches the date selected in a drop down, I want it to take all the information in that row and copy it into a completely separate work sheet, sorted in order by sales type. This is why I've been looking at vlookup and index match

 

[FDibbins]

That formula will work for what you put in your example, but that example is not at all what I'm looking for it to do.

My answer was based on the sample data provided by you. If that is not representative of what you are using, I would have no way to know that, so please show data that is more representative of what you are using

 

[MRobi]

My answer was based on the sample data provided by you. If that is not representative of what you are using, I would have no way to know that, so please show data that is more representative of what you are using

I don't know how I can show a sample any clearer than what I put here already (quoted again below)

There first table shown is a sample of what a salesperson will enter on the sheet. This data will change daily and end up thousands and thousands of rows over the year.

The second table shown is what I want as a result on a completely different worksheet. You'll notice the only thing being shown is Sold and Sold Split.

I'm not looking to just display the amount again in a single cell in a column at the end.

I'll use row 9 in the below table as an example. Because it shows as sold, and because it's dated 10/1/2017 I want that entire row (IE: All the info in column A, B,C, D, E) to display on a different worksheet. But because it's a national, I want it displayed above the info that's in Row 8 and below the info that's in Row 7 making the table on the 2nd worksheet look exactly like the 2nd table shown.

I don't think SUMIFS will do what I'm looking for. And no, the dates are all really dates, but the way the form is setup they're always the 1st day of the month. So 9/1/2017, 10/1/2017, 11/1/2017, etc...

Here's an idea of what the sales guys would fill out

1	A	B	C	D	E
2	Pipeline Phase	Date	Company	Sale Type	Amount
3	Prospect	9/1/2017	Company A	National	$500
4	Quoted	9/1/2017	Company B	New	$450
5	Sold	9/1/2017	Company C	New	$550
6	Following Up	10/1/2017	Company D	Existing	$700
7	Sold Split	10/1/2017	Company E	Existing	$1500
8	Sold	10/1/2017	Company F	New	$750
9	Sold	10/1/2017	Company G	National	$600
10	Sold	11/1/2017	Company H	Existing	$675

<tbody>
</tbody>

Here is what I'm looking to return on another worksheet if I select the month of Oct

Pipeline Phase	Date	Company	Sale Type	Amount
Sold Split	10/1/2017	Company E	Existing	$1500
Sold	10/1/2017	Company G	National	$600
Sold	10/1/2017	Company F	New	$750

<tbody>
</tbody>

 

[AhoyNC]

Maybe the example below will give you a start. It will bring the data over without blank rows, but won't sort the way you want.

The formula in F2 is an array formula that must be entered with CTRL-SHIFT-ENTER and then drag down column as needed.
Formula in G2 needs to be dragged down and across as needed.

Excel Workbook
	C	D	E	F	G	H	I	J	K
1		Month		Row Index	Pipeline Phase	Date	Company	Sale Type	Amount
2		10/1/2017		5	Sold Split	10/1/2017	Company E	Existing	1500
3				6	Sold	10/1/2017	Company F	New	750
4				7	Sold	10/1/2017	Company G	National	600
5
Sheet2

Excel Workbook
	A	B	C	D	E	F
1	Pipeline Phase	Date	Company	Sale Type	Amount	Helper
2	Prospect	9/1/2017	Company A	National	$500	0
3	Quoted	9/1/2017	Company B	New	$450	0
4	Sold	9/1/2017	Company C	New	$550	0
5	Following Up	10/1/2017	Company D	Existing	$700	0
6	Sold Split	10/1/2017	Company E	Existing	$1,500	1
7	Sold	10/1/2017	Company F	New	$750	1
8	Sold	10/1/2017	Company G	National	$600	1
9	Sold	11/1/2017	Company H	Existing	$675	0
Sheet1

 

[MRobi]

Maybe the example below will give you a start. It will bring the data over without blank rows, but won't sort the way you want.

The formula in F2 is an array formula that must be entered with CTRL-SHIFT-ENTER and then drag down column as needed.
Formula in G2 needs to be dragged down and across as needed.

Sheet2

	C	D	E	F	G	H	I	J	K
1		Month		Row Index	Pipeline Phase	Date	Company	Sale Type	Amount
2		10/1/2017		5	Sold Split	10/1/2017	Company E	Existing	1500
3				6	Sold	10/1/2017	Company F	New	750
4				7	Sold	10/1/2017	Company G	National	600
5

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:79px;"><col style="width:39px;"><col style="width:85px;"><col style="width:116px;"><col style="width:100px;"><col style="width:86px;"><col style="width:87px;"><col style="width:80px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F2 {=IFERROR(SMALL(IF(Sheet1!$F$2:$F$9=1,ROW(Sheet1!$A$2:$A$9)-ROW(Sheet1!$A$2)+1),ROWS($F$2:F2)),"")} G2 =IF($F2="","",INDEX(Sheet1!$A$2:$F$9,$F2,MATCH(G$1,Sheet1!$A$1:$E$1,0))) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Sheet1

	A	B	C	D	E	F
1	Pipeline Phase	Date	Company	Sale Type	Amount	Helper
2	Prospect	9/1/2017	Company A	National	$500	0
3	Quoted	9/1/2017	Company B	New	$450	0
4	Sold	9/1/2017	Company C	New	$550	0
5	Following Up	10/1/2017	Company D	Existing	$700	0
6	Sold Split	10/1/2017	Company E	Existing	$1,500	1
7	Sold	10/1/2017	Company F	New	$750	1
8	Sold	10/1/2017	Company G	National	$600	1
9	Sold	11/1/2017	Company H	Existing	$675	0

<colgroup><col style="width:30px; "><col style="width:113px;"><col style="width:98px;"><col style="width:99px;"><col style="width:116px;"><col style="width:73px;"><col style="width:68px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F2 =IF(MONTH(B2)=MONTH(Sheet2!$D$2),IF(OR(A2="Sold Split",A2="Sold"),1,0),0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This does do what I'm looking for in the way of populating Sheet2, just doesn't sort it. Definitely gives me a start! Thanks.

I wonder if more IF statements in the array formula would be able to return the parameters in the order I'm looking for?