Match Multiple Items based on Multiple Criteria

M

MRobi

New Member
Joined
Oct 26, 2017
Messages
7
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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)
 
Upvote 0
FDibbins said:
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)
Click to expand...
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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pipeline Phase[/TD]
[TD]Date[/TD]
[TD]Company[/TD]
[TD]Sale Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Prospect[/TD]
[TD]9/1/2017[/TD]
[TD]Company A[/TD]
[TD]National[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Quoted[/TD]
[TD]9/1/2017[/TD]
[TD]Company B[/TD]
[TD]New[/TD]
[TD]$450[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sold[/TD]
[TD]9/1/2017[/TD]
[TD]Company C[/TD]
[TD]New[/TD]
[TD]$550[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Following Up[/TD]
[TD]10/1/2017[/TD]
[TD]Company D[/TD]
[TD]Existing[/TD]
[TD]$700[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Sold Split[/TD]
[TD]10/1/2017[/TD]
[TD]Company E[/TD]
[TD]Existing[/TD]
[TD]$1500[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Sold[/TD]
[TD]10/1/2017[/TD]
[TD]Company F[/TD]
[TD]New[/TD]
[TD]$750[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Sold[/TD]
[TD]10/1/2017[/TD]
[TD]Company G[/TD]
[TD]National[/TD]
[TD]$600[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Sold[/TD]
[TD]11/1/2017[/TD]
[TD]Company H[/TD]
[TD]Existing[/TD]
[TD]$675[/TD]
[/TR]
</tbody>[/TABLE]

Here is what I'm looking to return on another worksheet if I select the month of Oct
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Pipeline Phase[/TD]
[TD]Date[/TD]
[TD]Company[/TD]
[TD]Sale Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Sold Split[/TD]
[TD]10/1/2017[/TD]
[TD]Company E[/TD]
[TD]Existing[/TD]
[TD]$1500[/TD]
[/TR]
[TR]
[TD]Sold[/TD]
[TD]10/1/2017[/TD]
[TD]Company G[/TD]
[TD]National[/TD]
[TD]$600[/TD]
[/TR]
[TR]
[TD]Sold[/TD]
[TD]10/1/2017[/TD]
[TD]Company F[/TD]
[TD]New[/TD]
[TD]$750[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
MRobi said:
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...
Click to expand...

Don't see why it wont work?
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]Pipeline Phase[/td][td]Date[/td][td]Company[/td][td]Sale Type[/td][td]Amount[/td][/tr]

[tr][td]
2​
[/td][td]Prospect[/td][td]9/1/2017[/td][td]Company A[/td][td]National[/td][td]$500 [/td][/tr]

[tr][td]
3​
[/td][td]Quoted[/td][td]9/1/2017[/td][td]Company B[/td][td]New[/td][td]$450 [/td][/tr]

[tr][td]
4​
[/td][td]Sold[/td][td]9/1/2017[/td][td]Company C[/td][td]New[/td][td]$550 [/td][/tr]

[tr][td]
5​
[/td][td]Following Up[/td][td]10/1/2017[/td][td]Company D[/td][td]Existing[/td][td]$700 [/td][/tr]

[tr][td]
6​
[/td][td]Sold Split[/td][td]10/1/2017[/td][td]Company E[/td][td]Existing[/td][td]$1,500 [/td][/tr]

[tr][td]
7​
[/td][td]Sold[/td][td]10/1/2017[/td][td]Company F[/td][td]New[/td][td]$750 [/td][/tr]

[tr][td]
8​
[/td][td]Sold[/td][td]10/1/2017[/td][td]Company G[/td][td]National[/td][td]$600 [/td][/tr]

[tr][td]
9​
[/td][td]Sold[/td][td]11/1/2017[/td][td]Company H[/td][td]Existing[/td][td]$675 [/td][/tr]
[/table]

[Table="width:, class:grid"][tr][td] [/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][td]
L​
[/td][/tr]
[tr][td]
1​
[/td][td]Pipeline Phase[/td][td]Date[/td][td]Company[/td][td]Sale Type[/td][td]Amount[/td][td]sumifs[/td][/tr]

[tr][td]
2​
[/td][td]Sold Split[/td][td]10/1/2017[/td][td]Company E[/td][td]Existing[/td][td]$1,500 [/td][td]
1500​
[/td][/tr]

[tr][td]
3​
[/td][td]Sold[/td][td]10/1/2017[/td][td]Company G[/td][td]National[/td][td]$600 [/td][td]
600​
[/td][/tr]

[tr][td]
4​
[/td][td]Sold[/td][td]10/1/2017[/td][td]Company F[/td][td]New[/td][td]$750 [/td][td]
750​
[/td][/tr]
[/table]
L2=SUMIFS(E:E,A:A,G2,B:B,H2,C:C,I2,D:D,J2)
copied down as needed
 
Upvote 0
FDibbins said:
Don't see why it wont work?
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]Pipeline Phase[/td][td]Date[/td][td]Company[/td][td]Sale Type[/td][td]Amount[/td][/tr]

[tr][td]
2​
[/td][td]Prospect[/td][td]9/1/2017[/td][td]Company A[/td][td]National[/td][td]$500 [/td][/tr]

[tr][td]
3​
[/td][td]Quoted[/td][td]9/1/2017[/td][td]Company B[/td][td]New[/td][td]$450 [/td][/tr]

[tr][td]
4​
[/td][td]Sold[/td][td]9/1/2017[/td][td]Company C[/td][td]New[/td][td]$550 [/td][/tr]

[tr][td]
5​
[/td][td]Following Up[/td][td]10/1/2017[/td][td]Company D[/td][td]Existing[/td][td]$700 [/td][/tr]

[tr][td]
6​
[/td][td]Sold Split[/td][td]10/1/2017[/td][td]Company E[/td][td]Existing[/td][td]$1,500 [/td][/tr]

[tr][td]
7​
[/td][td]Sold[/td][td]10/1/2017[/td][td]Company F[/td][td]New[/td][td]$750 [/td][/tr]

[tr][td]
8​
[/td][td]Sold[/td][td]10/1/2017[/td][td]Company G[/td][td]National[/td][td]$600 [/td][/tr]

[tr][td]
9​
[/td][td]Sold[/td][td]11/1/2017[/td][td]Company H[/td][td]Existing[/td][td]$675 [/td][/tr]
[/table]

[Table="width:, class:grid"][tr][td] [/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][td]
L​
[/td][/tr]
[tr][td]
1​
[/td][td]Pipeline Phase[/td][td]Date[/td][td]Company[/td][td]Sale Type[/td][td]Amount[/td][td]sumifs[/td][/tr]

[tr][td]
2​
[/td][td]Sold Split[/td][td]10/1/2017[/td][td]Company E[/td][td]Existing[/td][td]$1,500 [/td][td]
1500​
[/td][/tr]

[tr][td]
3​
[/td][td]Sold[/td][td]10/1/2017[/td][td]Company G[/td][td]National[/td][td]$600 [/td][td]
600​
[/td][/tr]

[tr][td]
4​
[/td][td]Sold[/td][td]10/1/2017[/td][td]Company F[/td][td]New[/td][td]$750 [/td][td]
750​
[/td][/tr]
[/table]
L2=SUMIFS(E:E,A:A,G2,B:B,H2,C:C,I2,D:D,J2)
copied down as needed
Click to expand...

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
 
Upvote 0
MRobi said:
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.
Click to expand...

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
 
Upvote 0
FDibbins said:
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
Click to expand...
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.

MRobi said:
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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pipeline Phase[/TD]
[TD]Date[/TD]
[TD]Company[/TD]
[TD]Sale Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Prospect[/TD]
[TD]9/1/2017[/TD]
[TD]Company A[/TD]
[TD]National[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Quoted[/TD]
[TD]9/1/2017[/TD]
[TD]Company B[/TD]
[TD]New[/TD]
[TD]$450[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sold[/TD]
[TD]9/1/2017[/TD]
[TD]Company C[/TD]
[TD]New[/TD]
[TD]$550[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Following Up[/TD]
[TD]10/1/2017[/TD]
[TD]Company D[/TD]
[TD]Existing[/TD]
[TD]$700[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Sold Split[/TD]
[TD]10/1/2017[/TD]
[TD]Company E[/TD]
[TD]Existing[/TD]
[TD]$1500[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Sold[/TD]
[TD]10/1/2017[/TD]
[TD]Company F[/TD]
[TD]New[/TD]
[TD]$750[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Sold[/TD]
[TD]10/1/2017[/TD]
[TD]Company G[/TD]
[TD]National[/TD]
[TD]$600[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Sold[/TD]
[TD]11/1/2017[/TD]
[TD]Company H[/TD]
[TD]Existing[/TD]
[TD]$675[/TD]
[/TR]
</tbody>[/TABLE]

Here is what I'm looking to return on another worksheet if I select the month of Oct
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Pipeline Phase[/TD]
[TD]Date[/TD]
[TD]Company[/TD]
[TD]Sale Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Sold Split[/TD]
[TD]10/1/2017[/TD]
[TD]Company E[/TD]
[TD]Existing[/TD]
[TD]$1500[/TD]
[/TR]
[TR]
[TD]Sold[/TD]
[TD]10/1/2017[/TD]
[TD]Company G[/TD]
[TD]National[/TD]
[TD]$600[/TD]
[/TR]
[TR]
[TD]Sold[/TD]
[TD]10/1/2017[/TD]
[TD]Company F[/TD]
[TD]New[/TD]
[TD]$750[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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
CDEFGHIJK
1MonthRow IndexPipeline PhaseDateCompanySale TypeAmount
210/1/20175Sold Split10/1/2017Company EExisting1500
36Sold10/1/2017Company FNew750
47Sold10/1/2017Company GNational600
5
Sheet2
Excel Workbook
ABCDEF
1Pipeline PhaseDateCompanySale TypeAmountHelper
2Prospect9/1/2017Company ANational$5000
3Quoted9/1/2017Company BNew$4500
4Sold9/1/2017Company CNew$5500
5Following Up10/1/2017Company DExisting$7000
6Sold Split10/1/2017Company EExisting$1,5001
7Sold10/1/2017Company FNew$7501
8Sold10/1/2017Company GNational$6001
9Sold11/1/2017Company HExisting$6750
Sheet1
 
Upvote 0
AhoyNC said:
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

CDEFGHIJK
Sold SplitCompany EExisting
SoldCompany FNew
SoldCompany GNational

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Month[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Row Index[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Pipeline Phase[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Company[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Sale Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Amount[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]10/1/2017[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10/1/2017[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10/1/2017[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10/1/2017[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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

ABCDEF
ProspectCompany ANational
QuotedCompany BNew
SoldCompany CNew
Following UpCompany DExisting
Sold SplitCompany EExisting
SoldCompany FNew
SoldCompany GNational
SoldCompany HExisting

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Pipeline Phase[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Company[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Sale Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Helper[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]9/1/2017[/TD]

[TD="align: right"]$500 [/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]9/1/2017[/TD]

[TD="align: right"]$450 [/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]9/1/2017[/TD]

[TD="align: right"]$550 [/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]10/1/2017[/TD]

[TD="align: right"]$700 [/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]10/1/2017[/TD]

[TD="align: right"]$1,500 [/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]10/1/2017[/TD]

[TD="align: right"]$750 [/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]10/1/2017[/TD]

[TD="align: right"]$600 [/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]11/1/2017[/TD]

[TD="align: right"]$675 [/TD]
[TD="align: right"]0[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
Click to expand...
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
How to return comma separated data (already set up) and return data into separately (matched) cells
Replies
3
Views
190
Fluff
Fluff
A
  • Solved
Filter data in listbox on form based on matching part of item in two comboboxes
Replies
9
Views
142
Ali M
A
C
  • Solved
Split into sheets based on multiple criteria
Replies
6
Views
195
cdrobinson83
C
A
  • Question Question
Index match match not using second set of match criteria
Replies
3
Views
171
Fluff
Fluff
M
  • Solved
Index and Match
Replies
3
Views
133
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top