Filter Certain Columns based on criteria

Microsoft Excel 2007
October 14, 2009 at 05:40:31
Specs: Windows Vista
How would I write a formula to achieve this?

This is an example from Microsoft. Where Car
Type (Column B) = Guzzler, return columns A,
C, E.

I basically need to create a View based on the
preset conditions, I was going to dump this
into a separate sheet, so that Sheet 1 is
master data, Sheet 2 would be "Guzzler"
Sheet 3 would be "Belcher" and Sheet 4 would
be "All data where Column A="Wang""

Thank you VERY Much for your assistance!

See More: Filter Certain Columns based on criteria

Report •

October 15, 2009 at 10:02:48

Your link doesn't work, so it is not possible to see what you are trying to achieve.

Please edit your post and test that the link works.

Also what do you mean by a 'View' and what are your 'preset conditions'


Report •

October 17, 2009 at 11:42:09
I am not sure why the link did not post correct. <a
AssetID=ZA010900521033">Let's try it this way.

View - I want to create a separate sheet showing only the
data that is in the preset array formula to report back.

Preset Conditions - whatever I determine to be the limiting
factor, in this example, "Guzzler" or "Belcher" would be a
limiter or the salesperson would be another example.

Report •

October 17, 2009 at 14:41:48

There are various ways to go about this.

I would first of all complete the Sales Person column, so that every row is complete.

In A3 put the formula =A2, then copy the formula into the empty cells in column A

This is what the data looks like:

A	B		C	D	E
1	Sales		Car	Number	Unit	Total
	Person		Type	Sold	Price	Sales
2	Barnhill	Guzzler	 5	2200	11000
3	Barnhill	Belcher	 4	1800	7200
4	Ingle		Guzzler	 6	2300	13800
5	Ingle		Belcher	 8	1700	13600
6	Jordan		Guzzler	 3	2000	6000
7	Jordan		Belcher	 1	1600	1600
8	Pike		Guzzler	 9	2150	19350
9	Pike		Belcher	 5	1950	9750
10	Sanchez		Guzzler	 6	2250	13500
11	Sanchez		Belcher	 8	2000	16000
12	Teal		Guzzler	 6	2500	15000
13	Teal		Belcher	 7	1900	13300
14	Wang		Guzzler	 4	2200	8800
15	Wang		Belcher	 3	2000	6000
16	Young		Guzzler	 8	2300	18400
17	Young		Belcher	 8	2100	16800

The simple way is to use the Data - Filter - Auto Filter which will allow you to select any entry from any column, or any range in any column.
Select A1 to E1
From the tollbar Data-Filter - Auto Filter
You will get a drop down list for each column.
Select any value to filter the list.
Use Custom from the drop-down to select ranges e.g., Sales >5

Select the resulting filtered list , Copy and Paste to a new sheet.

To do more interactive selection, you can use Excel's database functions
I will do this on the same sheet as it is easier to see what is happening

The database function requires two areas to be set up
1. Criteria
2. Results

In this example I am using three criteria:
a. Sales Person
b. Car type
c. Unit price

Create the following:

	G		H		I
3			Min		2000
4			Max		2500
5	Sales Person	Car Type	Price

Note that 'Sales Person' and 'Car Type' are identical to headings in the source database
Price is not the same

Create a list of sales person names and a list of car types

In cell G6 add data - validation - List and use the list of salesperson names plus one blank cell at the end
In cell H6 do the same for the car type
In cell I6 enter the formula: =AND(D2>=$I$3,D2<=$I$4)
This formula uses the max and min values you enter just above in cells I3 and I4 and they must have absolute references (i.e., with the $ signs)
D2 has no $ signs and is the first cell under the heading you want to filter by - in this case Unit Price.

Now a results section

	G		H		I
8	Number Sold	Unit Price	Total Sales

The headings in row 8 must be identical to the column headings in the Source database

In cell G9 enter the formula:     =DSUM($A$1:$E$17,G8,$G$5:$I$6)
In cell H9 enter the formula: =DAVERAGE($A$1:$E$17,H8,$G$5:$I$6)
In cell I9 enter the formula:     =DSUM($A$1:$E$17,I8,$G$5:$I$6)

The formulas basically all start with the whole range of the database including the headings (essential)
then the heading of the column to be calculated (can be entered as text or, as here, as a reference to the cell above
and finally the two rows that make up the criteria.

Now make selections in the criteria cells, such as selecting sales person Barnhill and no car type and price between 2000 and 2500

The result:

Number Sold	Unit Price	Total Sales
5		2200		11000

To add information to the result section use additional formulas, for example in cell F9 enter =IF(G6="","All",G6)
This will show the Sales person, or 'All' if the results include all sales persons.

Lookup the other Database functions that can be used.

Hope this gives you some ideas for what can be done.

There are many other ways with ordinary formulas or even array formulas (which are a very specific type of formula in Excel). I don't think that you were referring to array formulas such as this {=SUM(IF($B$2:$B$17="Belcher",IF($A$2:$A$17="Jordan",$E$2:$E$17,0),0))}
which are entered with Ctrl+Shift+Enter and have curly brackets, when you talked about 'preset array formula', but I may have been mistaken.

Hope this gives you some ideas.


Report •

Related Solutions

October 17, 2009 at 15:12:39
WOW, what an answer, and THANK YOU!

I have learned a lot from your posting and you have helped me
with 2/3 of the project - amazing.

The last bit is what I will call a "quick link". I am generating a
report that says something along the lines of "We had to fire
Jordan because he sold less than everyone else" and I need
to link to a sheet that will only show Jordan's results. But I do
need to show his results and hide the rest. And I would like
to do this dynamically - just by changing ="CRITERIA" in the
array formula.

In a nutshell - function like a database SELECT WHERE
statement would but by using Excel (Google Spreadsheets,
actually, so the Filter/ Auto Filter tool would be simple but not

Thanks again, you are amazing!

Report •

October 18, 2009 at 07:31:49

The following should give you an idea of how to achieve what you want.

I haven't attempted to do it exactly, but you should have enough information to modify it to meet your needs.

The Database is in A1 to E17 (including headers)

1. You need to create the statistics you need such as average and minimum so that you can select who to let go!

1 A create the raw data
Create two columns, One headed Total sales and the second (must be to the right of Total sales) headed Sales person.
Put the list of sales persons in the second column

	H	I
1	Total	Sales
	Sales	Person
2	18200	Barnhill
3	27400	Ingle
4	7600	Jordan
5	29100	Pike
6	29500	Sanchez
7	28300	Teal
8	14800	Wang
9	35200	Young

The Total sales uses the SUMIF() function that sums one column if the value in another column matches a 'criteria'
The formula in H2 is: =SUMIF($A$2:$A$17,I2,$E$2:$E$17)
Note the $ signs.
I2 contains the criteria, in this case a sales person name.
A2 to A17 is the column of sales person names in the database
E2 to E17 is the range to sum. These two ranges must be identical sizes.

Drag the formula down from H2.

1 B create the statistics

	G		H	I
10	Average		23762.5	
11	Max		35200	Young
12	Min		7600	Jordan
13	No. staff	8	

The formulas are:
H11 =MAX(H2:H9)
H12 =MIN(H2:H9)
H13 =COUNTA(I2:I9)

To find the staff with the lowest sales, use VLOOKUP to find the lowest value and return the adjacent name.
Vlookup only looks up a value in the leftmost column of a range and returns the value from the matching row in a column to the right of the lookup column. Thats why the sales person names must be to the right of the Total sales.

In I12 put this formula:=VLOOKUP(H12,$H$2:$I$9,2,FALSE)

2. Create your final message
Put pieces of text in a series of cells and use "&" to concatenate (join) pieces of text and the relevant statistics.

3.The final result came out like this:
We have had to let go Jordan because his average sales were $7600, which was the lowest of all our 8 sales staff, who averaged $23762.5

4. You could use a textbox to hold the text.
Then click on the textbox frame and in the formula bar enter the cell address of your finished text.


Report •

Ask Question