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:
This is the Dashboard that I'm wanting to pull it into:
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.
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.
