Alternative to multiple queries

[aka_krakur]

I have a worksheet that pulls its data from a query. Engineers have up to 5 different Product Family's that they oversee. Each of these Product Family's they like to have data on various failure modes (which is in another column); however, the failure modes are codes and an individual incident could have multiple failure codes. So, the current state of pulling this data from them is to do a different query on each failure mode which then summarizes the info into a pivot table and then that pivot table's information is copied and pasted into a "Dashboard". I'm thinking that there should be a way to do just one query on a particular Product Family and then have a vlookup or something to do the summarizing into the Dashboard.

Does anyone have any suggestions, or known formulas that can perform this?

By the way the failure codes are separated by a semi-colon (like so: 1586 - Bent; 1487 - N/A; 1185 - Patient) ..etc.

 

[aka_krakur]

Ok, I'm getting closer. I found a formula that worked on a set of sample data. But I can't get it to work with mine. Not sure what I'm doing wrong.

Sample Data:

2006 TR Log Carotid Dashboard PPG.xls
	A	B	C	D
1	Region	Salesperson	Type	Sales
2	South	Buchanan	Beverages	3571
3	West	Davolio	Dairy	3338
4	East	Suyama	Beverages	5122
5	North	Suyama	Dairy	6239
6	South	Dodsworth	Produce	8677
7	South	Davolio	Meat	450
8	South	Davolio	Meat	7673
9	East	Suyama	Produce	664
10	North	Davolio	Produce	1500
11	South	Dodsworth	Meat	6596
Sheet1

Formula

{=COUNT(IF((A2:A11="South")*(C2:C11="Meat"),D2:D11))}

The above formula is an array formula so you have to use the CTRL+SHIFT+ENTER to get the formula to work. Otherwise you'll get an #Value! error

I tried it with my data and I just subbed out the fields where I thought I should sub them.

Here's what my formula looked like:

{=COUNT(IF((ReportableQuery!L2:L702="RX Acculink")*(ReportableQuery!Z2:Z702="*1069 - *"),ReportableQuery!D2:D702))}

Mine comes back with a result of zero. Can't I use the "wildcards" in this formula?  If I type in an exact failure code it works to the key.  Like if I sub out *1069 - * with X100 - N/A it works.  But I need to be more general because there are too many variations after the "-" for example for 1158 these are the variations.  I need the formula to say if it begins with 1158 then count it.  Any suggestions now...I'm getting closer.

1158 - N/A
1158 - N/A
1158 - N/A
1158 - Filter Basket
1158 - Filter Basket
1158 - Filter Basket
1158 - Filter Basket
1158 - Filter Basket
1158 - N/A
1158 - Filter Basket
1158 - N/A
1158 - Filter Basket
1158 - N/A
1158 - N/A
1158 - Filter Basket
1158 - Filter Basket
1158 - N/A
1158 - Filter Basket
1158 - N/A
1158 - Filter Basket
1158 - N/A

 

[aka_krakur]

Where I need the most assistance is on the date. I also need to add in an if A2:A65536=1/1/06 as well. It's not liking it. Do I need to add # or " in front?