Multiple Search Criteria in Excel Table

[Tuffman]

I need help in doing the following please.

I first need to search down column B (the MAKE column) on the Cars worksheet to find the Toyota vehicles. Once the search finds Toyota it needs to record the first MODEL found from column A and paste/insert that once into cell B2 on the Summary worksheet. Then while the model type is the stays the same, it would search for all Used (the CONDITION column) cars where the YEAR is 2000 or newer, and count the number of Red, White, and Blue vehicles in the COLOR column and provide those answers in cells B3, B4, and B5 respectively on the Summary worksheet. If the YEAR has blanks, then the search would ignore the information in that row. Once the search finds the next Toyota MODEL it would record that in C2 on the Summary worksheet and then count the number of Red, White, and Blue Used vehicles that are the year of 2000 or newer. Below is the sheet to search on, a blank, summary table to record the data, and an example table of what the summary data should look like.

Car Example.xlsx
	A	B	C	D	E
1	MODEL	MAKE	COLOR	YEAR	CONDITION
2	Taurus	Ford	White	2000	Used
3	Taurus	Ford	Blue	1995	Wrecked
4	Taurus	Ford	Red	2007	Used
5	Taurus	Ford	Red	2010	Wrecked
6	Taurus	Ford	Blue	1990	Used
7	Taurus	Ford	Blue	1994	Wrecked
8	Taurus	Ford	White	2011	Used
9	Camry	Toyota	Red	2020	Wrecked
10	Camry	Toyota	White	2013	Used
11	Camry	Toyota	Blue	1997	Wrecked
12	Camry	Toyota	White	2000	Used
13	Camry	Toyota	Blue	2010	Wrecked
14	Camry	Toyota	Red	2007	Used
15	Camry	Toyota	Red	1994	Wrecked
16	Camry	Toyota	Blue	2017	Used
17	Camry	Toyota	Blue	2021	Wrecked
18	Camry	Toyota	White	2019	Used
19	Camry	Toyota	White	2020	Wrecked
20	Camry	Toyota	Red	1999	Used
21	Camry	Toyota	Red	2023	Wrecked
22	Camry	Toyota	White		Used
23	Camry	Toyota	Blue		Wrecked
24	Tacoma	Toyota	Red	2003	Used
25	Tacoma	Toyota	Blue	2017	Wrecked
26	Sierra	GMC	Red	1990	Used
27	Sierra	GMC	White	1994	Wrecked
28	Sierra	GMC	Blue	2011	Used
29	Sierra	GMC	White	2020	Wrecked
30	Sierra	GMC	Blue	2000	Used
31	Sierra	GMC	Red	2021	Wrecked
Cars

Car Example.xlsx
	A	B	C
2	Toyota
3	Red
4	White
5	Blue
6
7
8
9
10
11
12	Example
13	Toyota	Camry	Tacoma
14	Red	1	1
15	White	3	0
16	Blue	1	0
Summary

 

[KOKOSEK]

Book1
	A	B	C	D	E	F	G	H	I
1	MODEL	MAKE	COLOR	YEAR	CONDITION		Toyota	Camry	Tacoma
2	Taurus	Ford	White	2000	Used		Red	1	1
3	Taurus	Ford	Blue	1995	Wrecked		White	3	0
4	Taurus	Ford	Red	2007	Used		Blue	1	0
5	Taurus	Ford	Red	2010	Wrecked
Sheet1

Cell Formulas
Range	Formula
H2:I4	H2	=COUNTIFS($A:$A,H$1,$B:$B,$G$1,$C:$C,$G2,$D:$D,">=2000",$E:$E,"Used")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$E$31	H2:I4

 

[Sanjeev1976]

You can also use pivot table to achieve the required results :

Book5
	G	H	I	J
1	MAKE	Toyota
2	CONDITION	Used
3	YEAR	(Multiple Items)
4
5	Count of CONDITION	Column Labels
6	Row Labels	Camry	Tacoma	Grand Total
7	Red	1	1	2
8	White	4		4
9	Blue	1		1
10	Grand Total	6	1	7
Sheet1

 

[Tuffman]

Sorry, I need to know how to write the code in VBA to do what I described. My fault for not specifying that.

 

[KOKOSEK]

Really? VBA? For something what can be done with simple, quick, easy formula? That's your choice but I do not see the point.