COUNTIFS, VLOOKUP

[TrinaT]

Can you combine a countifs with a vlookup?

I have some data that has been broken down to an individual list, which I then need to collate back into a central Dashboard for one of our people to easily digest the information.

This is the table of data that I want to pull the information from:

Sample Data.xlsx
	A	B	C	D	E	F
1	First Name	Last Name	Column1	Item	Cost	Qty
2	Joe	Blogs	Joe Blogs	Adult Registration	165	1
3	Joe	Blogs	Joe Blogs	Adult Registration	165	1
4	John	Doh	John Doh	Mascot	27	1
5	John	Doh	John Doh	T-Shirt - Ladies (V-Neck)	40	1
6	John	Doh	John Doh	T-Shirt - Mens	40	2
7	John	Doh	John Doh	Adult Registration	165	1
8	John	Doh	John Doh	Adult Registration	165	1
9	Jessie	James	Jessie James	Hooded Sweat	85	1
10	Jessie	James	Jessie James	T-Shirt - Ladies (V-Neck)	40	1
11	Jessie	James	Jessie James	T-Shirt - Mens	40	1
12	Jessie	James	Jessie James	Adult Registration	165	1
13	Jessie	James	Jessie James	Adult Registration	165	1
14	Mark	Banks	Mark Banks	Adult Registration	165	1
15	Mark	Banks	Mark Banks	Adult Registration	165	1
16	Jeff	Jones	Jeff Jones	Cooler Bag	10	1
17	Jeff	Jones	Jeff Jones	T-Shirt - Ladies (V-Neck)	40	1
18	Jeff	Jones	Jeff Jones	T-Shirt - Mens	40	1
19	Jeff	Jones	Jeff Jones	Adult Registration	165	1
20	Jeff	Jones	Jeff Jones	Adult Registration	165	1
21	Shane	Williams	Shane Williams	Shirt - Long Sleeve	60	1
22	Shane	Williams	Shane Williams	T-Shirt - Mens	40	1
23	Shane	Williams	Shane Williams	Adult Registration	165	1
24	Shane	Williams	Shane Williams	Child Registration (under 12 years old)	100	1
25	Barry	Allen	Barry Allen	Cooler Bag	10	1
26	Barry	Allen	Barry Allen	Shirt - Long Sleeve	60	1
27	Barry	Allen	Barry Allen	T-Shirt - Mens	40	1
28	Barry	Allen	Barry Allen	Adult Registration	165	1
29	Mark	Mark	Mark Mark	Adult Registration	165	1
30	Mark	Mark	Mark Mark	Adult Registration	165	1
31	Daisy	Duke	Daisy Duke	Cooler Bag	10	1
32	Daisy	Duke	Daisy Duke	Shirt - Long Sleeve	60	1
33	Daisy	Duke	Daisy Duke	T-Shirt - Ladies (V-Neck)	40	1
34	Daisy	Duke	Daisy Duke	Adult Registration	165	1
35	Daisy	Duke	Daisy Duke	Adult Registration	165	1
tblSales

Cell Formulas
Range	Formula
C2:C35	C2	=CONCATENATE([@[First Name]]," ",[@[Last Name]])

This is the Dashboard that I'm wanting to pull it into:

Sample Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
18	Details
19	First Name	Last Name	Full Name	Adult Registration	Registration Child	T-Shirt Mens	Qty	Colour	Size	T-Shirt - Ladies (V-Neck)	Qty	Colour	Size	Shirt - Long Sleeve	Qty	Colour	Size	Hooded Sweat	Qty	Colour	Size	Mascot	Qty	Cooler Bag	Qty	Bar Runner	Qty
20	Joe	Blogs	Joe Blogs	0
21	John	Doh	John Doh
22	Jessie	James	Jessie James
23	Mark	Banks	Mark Banks
24	Jeff	Jones	Jeff Jones
25	Shane	Williams	Shane Williams
26	Barry	Allen	Barry Allen
27	Mark	Mark	Mark Mark
28	Daisy	Duke	Daisy Duke
Dashboard

Cell Formulas
Range	Formula
A20:B28	A20	='Export Data'!A2
C20:C28	C20	=CONCATENATE(A20," ",B20)
D20	D20	=COUNTIF(tblSales!F:F,VLOOKUP("*"&Dashboard!D19&"*",tblSales_2[[Column1]:[Qty]],4,0))

I've tried this formula:

=COUNTIF(tblSales!F:F,VLOOKUP("*"&Dashboard!D19&"*",tblSales_2[[Column1]:[Qty]],4,0))

But it's not correct. Essentially on the Dashboard, I want to Vlookup the Name, and countif there is a row that contains the text in the relevant column (e.g. Adult Registration, T-Shirt Mens etc.

 

[johnny51981]

You wouldn't need to use the VLOOKUP, but just COUNTIFS

Your D20 Formula would simply read as

=COUNTIFS(tblSales!$A:$A,Dashboard!$A20,tblSales!$B:$B,$B20,tblSales!$D:$D,Dashboard!D$19)

You actually wouldn't need the CONCATENATE function at all.