COUNTIF

[aka_krakur]

I need to somehow fix this formula to work. It's not working so far.
Here is a sample data set first:

Book2
	A	B	C	D
1	MONTH_SORT	INCIDENT_KEY	PRODUCT_FAMILY	REPORTED_DEV_CLARIFICATION
2	7/1/2006	77628-1-1	Product1	X100-N/A;
3	7/1/2006	77651-1-1	Product1	X100-N/A;
4	7/1/2006	77653-1-2	Product1	X100-N/A;
5	7/1/2006	77655-1-1	Product1	1158-N/A;1536-Handle;
6	7/1/2006	77656-1-2	Product1	X100-N/A;
7	7/1/2006	77656-1-3	Product1	X100-N/A;
8	7/1/2006	77661-1-2	Product2	2578-Stent;
9	7/1/2006	77662-1-1	Product1	X100-N/A;
10	7/1/2006	77676-1-1	Product1	X100-N/A;
11	7/1/2006	77684-1-1	Product1	X100-N/A;
12	7/1/2006	77686-1-1	Product1	1528-N/A;2203-IrregularAppearance;1670-IncorrectRemoval;
13	7/1/2006	77692-1-1	Product1	X100-N/A;
14	7/1/2006	77692-1-2	Product2	X100-N/A;
15	7/1/2006	77693-1-1	Product1	X100-N/A;
16	7/1/2006	77759-1-1	Product2	1158-FilterBasket;
17	8/1/2006	77764-1-2	Product1	X100-N/A;
18	8/1/2006	77768-1-1	Product3	2524-Lesion;
19	8/1/2006	77778-1-1	Product1	X100-N/A;
20	8/1/2006	77781-1-1	Product1	X100-N/A;
21	8/1/2006	77799-1-1	Product1	X100-N/A;
22	8/1/2006	77800-1-1	Product1	2593-Failure;
23	8/1/2006	77800-1-2	Product1	2593-Failure;
24	8/1/2006	77800-1-3	Product1	2593-Failure;
25	8/1/2006	77831-1-1	Product1	X100-N/A;
26	8/1/2006	77831-1-2	Product2	X100-N/A;
27	8/1/2006	77832-1-1	Product1	X100-N/A;
28	8/1/2006	77838-1-1	Product1	X100-N/A;
29	8/1/2006	77842-1-1	Product1	2524-NoPI-NoInformation;
30	8/1/2006	77848-1-1	Product1	X100-N/A;
31	8/1/2006	77848-1-2	Product2	2578-N/A;
32	8/1/2006	77852-1-1	Product1	1339-Shaft;2578-DeviceComponent;
33	8/1/2006	77878-1-2	Product1	X100-N/A;
34	3/1/2006	77880-1-1	Product2	1487-UnabletoFlush;2524-Lesion;
35	9/1/2006	77914-1-1	Product2	2524-Lesion;
36	9/1/2006	77914-1-2	Product1	X100-N/A;
37	9/1/2006	77916-1-1	Product1	X100-N/A;
38	9/1/2006	77974-1-1	Product3	2524-Lesion;
39	9/1/2006	78015-1-2	Product1	X100-N/A;
40	9/1/2006	78020-1-1	Product3	1393-N/A;1528-FilterBasket;
41	9/1/2006	78036-1-2	Product2	2578-Stent;
42	9/1/2006	78047-1-3	Product1	1158-N/A;1670-ExcessiveForce;1528-Vessel;1339-Shaft;
43	9/1/2006	78047-1-4	Product3	1586-Core;2578-N/A;
44	9/1/2006	78048-1-2	Product1	X100-N/A;
45	9/1/2006	78095-1-2	Product1	X100-N/A;
46	9/1/2006	78105-1-2	Product1	1069-Shaft;1670-UseAfterDamage;1339-Shaft;
47	9/1/2006	78107-1-1	Product3	1586-Tip;
48	9/1/2006	78143-1-1	Product1	X100-N/A;
49	9/1/2006	78145-1-1	Product1	X100-N/A;
50	9/1/2006	78162-1-2	Product1	X100-N/A;
51	9/1/2006	78170-1-1	Product3	2578-DeviceComponent;
52	9/1/2006	78176-1-1	Product1	X100-N/A;
53	9/1/2006	78179-1-1	Product3	2524-Lesion;
54	9/1/2006	78195-1-1	Product3	1601-Tip;
Sheet1

 

[JohnnyTightlips]

You may need to provide some more information, I couldn't see any formulas

 

[aka_krakur]

Oops. not sure why my formula didn't show up.

Okay,
so here's a run down of what I'm after. I need to get a total count of (let's say) Product1 (Column C) during the month of September (ColumnA) where the failure code begins with 2578 (Column D)

I can get an overall count using a wild card on a countif formula as such:

=countif(D2:D54,"*2578*")

But I need to be able to filter further by month and product family
I was also successful (if I could break up column D) by this formula:

{=count(if((C2:C54="Product1",if(D2:D54=(left(D2:AA54,4)="2578"),B2:B54))}

This was an array formula, so I had to use the CTRL+SHIFT+ENTER after entering the formula.

Couple problems I have with this method. I still wasn't able to write an if statement in there to select by month as well (it always returned a zero value) so I can't figure that out.
But I also can't break up Column D because I am trying to get the count by incident only, and there is the possibilty that at a given time the filter will be by 3 or more different failure codes which all 3 could be in the same incident which would only count as qty 1. Here's an example of that scenario:

Book2
	A	B	C	D
1	MONTH_SORT	INCIDENT_KEY	PRODUCT_FAMILY	REPORTED_DEV_CLARIFICATION
2	8/1/2006	67115-1-1	Product1	1158-Failure1;1484-N/A;1536-Failure3;1157-Unknown;
3	8/1/2006	67246-1-1	Product1	1467-Failure1;
4	8/1/2006	67290-1-1	Product2	1467-Failure2;
5	8/1/2006	67328-1-1	Product3	1158-N/A;
6	8/1/2006	67338-1-1	Product1	1536-NoInformation;
7	8/1/2006	67419-1-1	Product2	1586-Failure2
8	8/1/2006	67476-1-2	Product1	1157-Unknown;
9	8/1/2006	67552-1-1	Product4	1467-Failure1;
10	8/1/2006	67555-1-1	Product1	1467-Failure2;1586-N/A;
11	8/1/2006	67567-1-2	Product1	1158-N/A;1670-Failure3
12	8/1/2006	67592-1-1	Product2	1158-Failure1;1484-N/A;1536-Failure3;1157-Unknown;
13	8/1/2006	67600-1-1	Product3	1467-Failure1;
14	8/1/2006	67690-1-1	Product1	1467-Failure2;
15	9/1/2006	67695-1-1	Product3	1158-N/A;
16	9/1/2006	67695-1-2	Product1	1536-NoInformation;
17	9/1/2006	67706-1-1	Product1	1586-Failure2
18	9/1/2006	67708-1-1	Product2	1157-Unknown;
19	9/1/2006	67798-1-1	Product4	1467-Failure1;
20	9/1/2006	67879-1-2	Product1	1467-Failure2;1586-N/A;
21	9/1/2006	67881-1-1	Product3	1158-N/A;1670-Failure3
22	9/1/2006	67889-1-1	Product2	1158-Failure1;1484-N/A;1536-Failure3;1157-Unknown;
23	9/1/2006	67892-1-1	Product1	1467-Failure1;
Sheet1

Okay so let's say an engineer asked for (or wanted) the following:
Count of failures for August, 2006, Product1 with Failure codes: 1158, 1484, 1536, & 1157
I should get a count of 4.
because although the 1st row has all four (4) failure codes, it's one incident so it should only count as 1
Row 6 = 1
Row 8 = 1
Row 11 = 1
Total count = 4
with this set of data.
So, I really need to figure out a formula that can accomplish that.
And it seems like I should be able to write it with the array formula.
But for some reason I can't use the wildcard * in that formula (works in the countif) but if I write it like this:

{=count((if(C2:C23="Product1",if(D2:D23="*1158*"),B2:B23))}

it doesn't work. comes back with a zero (0) count.
Well, I hope that's enough information for someone to come to the rescue or some ideas/suggestions....anything. Please help.

 

[aka_krakur]

Okay, so I figured out the date problem in the if statement or countif statement, it would need to be A2:A23=38961 or the integer value of the date. Not a problem there. I can work with that.

I have tried so many different combinations of formulas. The latest one that I thought would work was this:

=IF(AND(A2:A23=38961,C2:C23="Product1"),COUNTIF(D2:D23,"*1467*",0)

Does anyone out there know how I might perfect this?

 

[Aladin Akyurek]

...
Okay so let's say an engineer asked for (or wanted) the following:
Count of failures for August, 2006, Product1 with Failure codes: 1158, 1484, 1536, & 1157
I should get a count of 4.
...

=SUMPRODUCT(--(DateRange-DAY(DateRange)+1="1-Aug-06"+0),--(ProductRange="Product1"),--ISNUMBER(MATCH(LEFT(FailureRange,4)+0,{1158,1484,1536,1157},0)))

It seems you have multiple distinct failure codes in some cells...

=SUMPRODUCT(--(DateRange-DAY(DateRange)+1="1-Aug-06"+0),--(ProductRange="Product1"),--ISNUMBER(SEARCH({1158,1484,1536,1157},FailureRange)))

 

[Brian from Maui]

Try one of the following,

=SUMPRODUCT(--($A$1:$A$10-DAY($A$1:$A$10)+1=$F$1),--($C$1:$C$10=$F$2),--(ISNUMBER(SEARCH(20,D1:D10))+ISNUMBER(SEARCH(21,D1:D10))+ISNUMBER(SEARCH(22,D1:D10))))

=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH({"20","21","22"},$D$1:$D$100))+0,ROW($D$1:INDEX($D$1:$D$100,COLUMNS({"20","21","22"})))^0)>0),--($C$1:$C$100="b"),--($A$1:$A$100-DAY($A$1:$A$100)+1=$F$1))

Substitute 20, 21, 22 with your failure code, F2 for product and F1 houses the first day of the month of interest. Adjust the ranges also.

 

[NBVC]

Here's an option:

I inserted a helper column that will count the record (once only) if any of the codes are found. This allows you to enter up to 4 failure codes in I2:L2. If you want to expand to allow for more then, change the L2 in the following formula, found in E2 copied down, to match the last possible cell reference that may contain an entry: =OR(ISNUMBER(SEARCH($I$2:INDEX($G$2:$L$2,MATCH(9.999999999E+307,$G$2:$L$2)),D2)))+0 confirmed with CTRL+SHIFT+ENTER.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	MONTH_SORT	INCIDENT_KEY	PRODUCT_FAMILY	REPORTED_DEV_CLARIFICATION	Finds		Date	Product	Failure Code 1	Failure Code 2	Failure Code 3	Failure Code 4
2	8/1/2006	67115-1-1	Product1	1158 - Failure1;1484 - N/A;1536 - Failure3; 1157 - Unknown;	1		1-Aug	Product1	1158	1484	1536	1157
3	8/1/2006	67246-1-1	Product1	1467 - Failure1;	0
4	8/1/2006	67290-1-1	Product2	1467 - Failure2;	0
5	8/1/2006	67328-1-1	Product3	1158 - N/A;	1		Number of Matches	4
6	8/1/2006	67338-1-1	Product1	1536 - No Information;	1
7	8/1/2006	67419-1-1	Product2	1586 - Failure2	0
8	8/1/2006	67476-1-2	Product1	1157 - Unknown;	1
9	8/1/2006	67552-1-1	Product4	1467 - Failure1;	0
10	8/1/2006	67555-1-1	Product1	1467 - Failure2;1586 - N/A;	0
11	8/1/2006	67567-1-2	Product1	1158 - N/A;1670 - Failure3	1
12	8/1/2006	67592-1-1	Product2	1158 - Failure1;1484 - N/A;1536 - Failure3; 1157 - Unknown;	1
13	8/1/2006	67600-1-1	Product3	1467 - Failure1;	0
Sheet1

Formula in E2 copied down: =OR(ISNUMBER(SEARCH($I$2:INDEX($G$2:$L$2,MATCH(9.999999999E+307,$G$2:$L$2)),D2)))+0 confirmed with Ctrl+Shift+Enter

Formula in H5 (for total record counts): =SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(G2)),--($C$2:$C$23=H2),$E$2:$E$23)

 

[aka_krakur]

wow, a lot of choices there. I will get back with you all when I find one of these that works.
Thanks for your time and help.

 

[aka_krakur]

Okay, so far I'm having more luck with NBVC (the guy with the smurf).
Only problem I run in to is sometimes the failure code is NOT a number. Can I just switch that to ISTEST(SEARCH(

I tried but it didn't work. And how about if there is only 1 failure code. Does it work to leave the rest of them blank and it will only search for the 1?

I'll keep trying to work with this though, it's a great step up from where I was at.

Thanks

 

[Aladin Akyurek]

Okay, so far I'm having more luck with NBVC (the guy with the smurf).
Only problem I run in to is sometimes the failure code is NOT a number. Can I just switch that to ISTEST(SEARCH(

I tried but it didn't work. And how about if there is only 1 failure code. Does it work to leave the rest of them blank and it will only search for the 1?

I'll keep trying to work with this though, it's a great step up from where I was at.

Thanks

Side-tracked by multiple error codes per row, I posted mindlessly a wrong formula. Brian had actually suggested the right idiom for a single formula approach. Here how it should read adapted to your original exhibit:

=SUMPRODUCT(--($A$2:$A$23-DAY($A$2:$A$23)+1="1-Aug-06"+0),--($C$2:$C$23="Product1"),--(MMULT(ISNUMBER(SEARCH({"1158","1484","1536","1157"},$D$2:$D$23))+0,ROW($D$2:INDEX($D$2:$D$1200,COLUMNS({"1158","1484","1536","1157"})))^0)>0))