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

 

[aka_krakur]

Yeah, I'm having a lot more luck with that formula now.

Couple Questions.

When I 1st posted this I made the Product_Family seem like it didn't matter too much.
It's a lot more complex than Product1, Product2, etc.
I can have one that's like Product1-Clinical; Product1-Test but I may want to pull all Product1's or I may want only Product1-Clinical.

So is there a way to do a search on that as well. Instead of just putting $C$2:$C$23="Product1" is there a way to search like we did with the failure codes.

2nd question:
not all failure codes are numbers. I may have a time where the failure code is text. Not sure if it's as simple as changing the ISNUMBER(SEARCH... to ISTEXT(SEARCH.

Please let me know.

This formula's working great other than that though.

Thanks,
David

 

[Aladin Akyurek]

Yeah, I'm having a lot more luck with that formula now.

Couple Questions.

When I 1st posted this I made the Product_Family seem like it didn't matter too much.
It's a lot more complex than Product1, Product2, etc.
I can have one that's like Product1-Clinical; Product1-Test but I may want to pull all Product1's or I may want only Product1-Clinical.

So is there a way to do a search on that as well. Instead of just putting $C$2:$C$23="Product1" is there a way to search like we did with the failure codes.

=SUMPRODUCT(--($A$2:$A$23-DAY($A$2:$A$23)+1="1-Aug-06"+0),--ISNUMBER(SEARCH("Product1-",$C$2:$C$23)),--(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))

2nd question:
not all failure codes are numbers. I may have a time where the failure code is text. Not sure if it's as simple as changing the ISNUMBER(SEARCH... to ISTEXT(SEARCH.

Please let me know.

ISNUMBER check is required for SEARCH returns numbers. The search substrings list can be expanded with other items like in:

{"1158","1484","1536","1157","FRAN"}

This formula's working great other than that though.

Thanks,
David

You are welcome.

 

[aka_krakur]

Thanks everyone for your help. I have the formula working 100% accurate now.
Thanks!!!

 

[aka_krakur]

One last & hopefully small easy question:

I have times when the product family is very similar.
For instance.
1 Product Family = RX Acculink
2 Product Family = RX Acculink 3-IN-1

There are times when I only want to count RX Acculink, times when I only want to count Acculink 3-IN-1 and other times when I want to count them all together.
Any ideas?

 

[NBVC]

Okay, so far I'm having more luck with NBVC (the guy with the smurf).
...
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?....

Thanks

Mine will work if you had one failure code..or two...or three....or four. As I had said you can easily expand it to allow more failure codes.

 

[Aladin Akyurek]

One last & hopefully small easy question:

I have times when the product family is very similar.
For instance.
1 Product Family = RX Acculink
2 Product Family = RX Acculink 3-IN-1

There are times when I only want to count RX Acculink, times when I only want to count Acculink 3-IN-1 and other times when I want to count them all together.
Any ideas?

You need something more to distinguish between them... For example:

RX Acculink -
RX Acculink 3-IN-1 -

would work.

Otherwise, you'll need to substract the result for RX Acculink 3-IN-1 from the result for RX Acculink.

 

[aka_krakur]

Yeah, I thought about the subtraction thing this weekend. That's what I'm going to have to do, because all the Product Families come from an Oracle Database that I query in to Excel, that the whole point of these macros and formulas and such is to automate the process and take away all the manual processes.

Thanks for the ideas

 

[aka_krakur]

More help with sumproduct formula

Once again I have stumbled with figuring out how to use this formula when I have two Products that are similar in name:

Product 1 = Accunet
Product 2 = Accunet 3-IN-1

Here's my formula I've been using successfully up until today:

=IF(ISERROR(SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH($C19:$F19,ReportableQuery!$Z$19:$Z$893))+0,ROW(ReportableQuery!$Z$19:INDEX(ReportableQuery!$Z$19:$Z$893,COLUMNS($C19:$F19)))^0)>0),--(MMULT(--ISNUMBER(SEARCH($B19,ReportableQuery!$L$19:$L$893))+0,ROW(ReportableQuery!$L$19:INDEX(ReportableQuery!$L$19:$L$893,COLUMNS($B19)))^0)>0),--(ReportableQuery!$I$19:$I$893-DAY(ReportableQuery!$I$19:$I$893)+1=N$3))),0,(SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH($C19:$F19,ReportableQuery!$Z$19:$Z$893))+0,ROW(ReportableQuery!$Z$19:INDEX(ReportableQuery!$Z$19:$Z$893,COLUMNS($C19:$F19)))^0)>0),--(MMULT(--ISNUMBER(SEARCH($B19,ReportableQuery!$L$19:$L$893))+0,ROW(ReportableQuery!$L$19:INDEX(ReportableQuery!$L$19:$L$893,COLUMNS($B19)))^0)>0),--(ReportableQuery!$I$19:$I$893-DAY(ReportableQuery!$I$19:$I$893)+1=N$3))))

Where C19:F19 are the failure codes to look up.
B19 contains the Product Name.
Here's where it gets tricky for me.
Scenario:
Product Accunet
Failure Codes 2578 - clarifier1, & 2578 - clarifier2
So when this formula goes and searches the ReportableQuery worksheet (Z19:Z893); it returns finds even for Accunet 3-IN-1.
I don't need/want these counted.

Can someone think of something to resolve this.
I tried doing a sum formula where I subtract the above formula - and then retype the entire formula and force the 2nd formula to look up only Accunet 3-IN-1. But Excel doesn't like it...too long of a formula.

PLEASE HELP!!!