COUNTIF

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
I need to somehow fix this formula to work. It's not working so far.
Here is a sample data set first:
Book2
ABCD
1MONTH_SORTINCIDENT_KEYPRODUCT_FAMILYREPORTED_DEV_CLARIFICATION
27/1/200677628-1-1Product1X100-N/A;
37/1/200677651-1-1Product1X100-N/A;
47/1/200677653-1-2Product1X100-N/A;
57/1/200677655-1-1Product11158-N/A;1536-Handle;
67/1/200677656-1-2Product1X100-N/A;
77/1/200677656-1-3Product1X100-N/A;
87/1/200677661-1-2Product22578-Stent;
97/1/200677662-1-1Product1X100-N/A;
107/1/200677676-1-1Product1X100-N/A;
117/1/200677684-1-1Product1X100-N/A;
127/1/200677686-1-1Product11528-N/A;2203-IrregularAppearance;1670-IncorrectRemoval;
137/1/200677692-1-1Product1X100-N/A;
147/1/200677692-1-2Product2X100-N/A;
157/1/200677693-1-1Product1X100-N/A;
167/1/200677759-1-1Product21158-FilterBasket;
178/1/200677764-1-2Product1X100-N/A;
188/1/200677768-1-1Product32524-Lesion;
198/1/200677778-1-1Product1X100-N/A;
208/1/200677781-1-1Product1X100-N/A;
218/1/200677799-1-1Product1X100-N/A;
228/1/200677800-1-1Product12593-Failure;
238/1/200677800-1-2Product12593-Failure;
248/1/200677800-1-3Product12593-Failure;
258/1/200677831-1-1Product1X100-N/A;
268/1/200677831-1-2Product2X100-N/A;
278/1/200677832-1-1Product1X100-N/A;
288/1/200677838-1-1Product1X100-N/A;
298/1/200677842-1-1Product12524-NoPI-NoInformation;
308/1/200677848-1-1Product1X100-N/A;
318/1/200677848-1-2Product22578-N/A;
328/1/200677852-1-1Product11339-Shaft;2578-DeviceComponent;
338/1/200677878-1-2Product1X100-N/A;
343/1/200677880-1-1Product21487-UnabletoFlush;2524-Lesion;
359/1/200677914-1-1Product22524-Lesion;
369/1/200677914-1-2Product1X100-N/A;
379/1/200677916-1-1Product1X100-N/A;
389/1/200677974-1-1Product32524-Lesion;
399/1/200678015-1-2Product1X100-N/A;
409/1/200678020-1-1Product31393-N/A;1528-FilterBasket;
419/1/200678036-1-2Product22578-Stent;
429/1/200678047-1-3Product11158-N/A;1670-ExcessiveForce;1528-Vessel;1339-Shaft;
439/1/200678047-1-4Product31586-Core;2578-N/A;
449/1/200678048-1-2Product1X100-N/A;
459/1/200678095-1-2Product1X100-N/A;
469/1/200678105-1-2Product11069-Shaft;1670-UseAfterDamage;1339-Shaft;
479/1/200678107-1-1Product31586-Tip;
489/1/200678143-1-1Product1X100-N/A;
499/1/200678145-1-1Product1X100-N/A;
509/1/200678162-1-2Product1X100-N/A;
519/1/200678170-1-1Product32578-DeviceComponent;
529/1/200678176-1-1Product1X100-N/A;
539/1/200678179-1-1Product32524-Lesion;
549/1/200678195-1-1Product31601-Tip;
Sheet1
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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:
Code:
=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:
Code:
{=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
ABCD
1MONTH_SORTINCIDENT_KEYPRODUCT_FAMILYREPORTED_DEV_CLARIFICATION
28/1/200667115-1-1Product11158-Failure1;1484-N/A;1536-Failure3;1157-Unknown;
38/1/200667246-1-1Product11467-Failure1;
48/1/200667290-1-1Product21467-Failure2;
58/1/200667328-1-1Product31158-N/A;
68/1/200667338-1-1Product11536-NoInformation;
78/1/200667419-1-1Product21586-Failure2
88/1/200667476-1-2Product11157-Unknown;
98/1/200667552-1-1Product41467-Failure1;
108/1/200667555-1-1Product11467-Failure2;1586-N/A;
118/1/200667567-1-2Product11158-N/A;1670-Failure3
128/1/200667592-1-1Product21158-Failure1;1484-N/A;1536-Failure3;1157-Unknown;
138/1/200667600-1-1Product31467-Failure1;
148/1/200667690-1-1Product11467-Failure2;
159/1/200667695-1-1Product31158-N/A;
169/1/200667695-1-2Product11536-NoInformation;
179/1/200667706-1-1Product11586-Failure2
189/1/200667708-1-1Product21157-Unknown;
199/1/200667798-1-1Product41467-Failure1;
209/1/200667879-1-2Product11467-Failure2;1586-N/A;
219/1/200667881-1-1Product31158-N/A;1670-Failure3
229/1/200667889-1-1Product21158-Failure1;1484-N/A;1536-Failure3;1157-Unknown;
239/1/200667892-1-1Product11467-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:
Code:
{=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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

...
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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
ABCDEFGHIJKL
1MONTH_SORTINCIDENT_KEYPRODUCT_FAMILYREPORTED_DEV_CLARIFICATIONFindsDateProductFailure Code 1Failure Code 2Failure Code 3Failure Code 4
28/1/200667115-1-1Product11158 - Failure1;1484 - N/A;1536 - Failure3; 1157 - Unknown;11-AugProduct11158148415361157
38/1/200667246-1-1Product11467 - Failure1;0
48/1/200667290-1-1Product21467 - Failure2;0
58/1/200667328-1-1Product31158 - N/A;1Number of Matches4
68/1/200667338-1-1Product11536 - No Information;1
78/1/200667419-1-1Product21586 - Failure20
88/1/200667476-1-2Product11157 - Unknown;1
98/1/200667552-1-1Product41467 - Failure1;0
108/1/200667555-1-1Product11467 - Failure2;1586 - N/A;0
118/1/200667567-1-2Product11158 - N/A;1670 - Failure31
128/1/200667592-1-1Product21158 - Failure1;1484 - N/A;1536 - Failure3; 1157 - Unknown;1
138/1/200667600-1-1Product31467 - 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

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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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))
 

Forum statistics

Threads
1,136,876
Messages
5,678,288
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top