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]

A little more info:
I did try out =countif(V2:V65536,"X100;")
This ended up working; however, it only counted fields in Column V that ONLY had X100; in it. If it contained let's say X100;1586;1069 it would not count that X100.

I feel like I'm right there on figuring this out; I just need a little help if someone knows another way to do this.

 

[tusharm]

When you mention "query," I think of SQL query possibly with the help of MS Query. But, that is inconsistent with COUNTIF.

So, what exactly does a query mean to you?

 

[aka_krakur]

I figured it out. I'm going to have a VBA macro that just splits the code column (Column V in this case) into multiple columns by the semicolon. Then I will just have the =countif statement widen the range
something like this: = countif(V2:AZ65536,"X100")


It worked!!!

 

[aka_krakur]

Actually, I might need some help again.
Is there a way to do a multiple criteria on the countif statement.
Something like Column C must equal "Product1" and Column A must equal 1/1/06, and Columns V:AZ must = "X100"

 

[aka_krakur]

I would think this should work logically; but it only returns a "#Value!"

=IF(A45:A738=3/1/06,IF(L45:L738="Product1", COUNTIF(AA45:IV65536,"1069"),0))

I'm wanting the formula to return the count if Columns AA through IV equal "1069" only if Column A = 3/1/06 & Column L = "Product1"

Any suggestions?

 

[aka_krakur]

I'm back at square one. I can't split up Column AA. I need to keep all the failure codes together; because some failure queries include multiple failures. So if I use the Countif formula their might be a combination of failure codes on one incident.

So let's say Incident contains failure codes 1158;1069;X100;1157

and the query being requested is for failure codes 1157, 1158, 1484, and 1536
the countif statement if this column was broken apart would count this as qty 2 when truly it is only 1 because it is one incident.
I need to count the failure codes per incident.
So the result should've only been qty 1.

Any suggestions on how I could accomplish this in a formula?

 

[aka_krakur]

Is there a way to do a countif=(range, like"X100")

 

[Norie]

Use wildcards.

=COUNTIF(A1:A100, "*X100*")

 

[aka_krakur]

Thanks,
do you know how to further that formula by stating if Column A = 3/1/06 (being that Column A is full of dates)

for example. I only want the count of all failure codes X100 when Column A = 3/1/06.