Alternative to multiple queries

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Active Member
Joined
Jan 31, 2006
Messages
438
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

Active Member
Joined
Jan 31, 2006
Messages
438

ADVERTISEMENT

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

Active Member
Joined
Jan 31, 2006
Messages
438
I would think this should work logically; but it only returns a "#Value!"
Code:
=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

Active Member
Joined
Jan 31, 2006
Messages
438

ADVERTISEMENT

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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,931
Office Version
  1. 365
Platform
  1. Windows
Use wildcards.

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

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,415
Messages
5,547,808
Members
410,813
Latest member
Vhinzvirgo
Top