Extract Data Using multiple criteria within a column

[Clint88]

I want to first say that this forum is a life saver! The solutions found at these two links have gotten me 95% to where I want to be.
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
http://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/

My general question is how do I add a criteria where there is multiple criteria in that column?

I have a sheet with info in columns A-J and varies between 800 and 900 rows. What I would like is to list the values from column I (Job Title) and column J (Total Hours) when the value in the correspoding cell in column C is 110, column D is 400020, and column H is ANY of the following (4863,4007,301065,CP0041,4764,2239,1791,301015,500103,CP0021,4533,4537,4538,4539,4541,SC0009,301145,301288).

I can get as far as the first two criteria using this formula derived from the above links (for column I):
=IF(COUNTIFS($C:$C,110,$D:$D,400020)<ROWS($N$6:$N6),"",(INDEX($I:$I,SMALL(IF(($C:$C=110)+($D:$D=400020)=2,ROW($C:$C)),ROW($A1)))))

The problem I run into is when I try to add in the third column criteria that has multiple choices within the column. I tried
=IF(COUNTIFS($C:$C,110,$D:$D,400020,$H:$H,{4863,4007,301065,"CP0041",4764,2239,1791,301015,500103,"CP0021",4533,4537,4538,4539,4541,"SC0009",301145,301288})<ROWS($N$6:$N6),"",(INDEX($I:$I,SMALL(IF(($C:$C=110)+($D:$D=400020)+($H:$H={4863,4007,301065,"CP0041",4764,2239,1791,301015,500103,"CP0021",4533,4537,4538,4539,4541,"SC0009",301145,301288})=3,ROW($C:$C)),ROW($A1)))))

But that doesn't return any value when plugged into a single cell and if I try to copy the formula to multiple cells at once I get "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated."

If anyone can please show me where I'm going wrong I would greatly appreciate it.

Help me Obi Wan Kenobi. You're my only hope!

 

[Mike86]

Try:

=AND(C6=110,D6=400020,OR(H6=4863,H6=4007,H6=301065,H6=CP41,H6=4764,H6=2239,H6=1791,H6=301015,H6=500103,H6=CP21,H6=4533,H6=4537,H6=4538,H6=4539,H6=4541,H6=SC9,H6=301145,H6=301288))

 

[Khalid NGO]

Hello Clint,
Good day and welcome to the forum

Give it a try: {array formula}

=IF(SUM(COUNTIFS($C$2:$C$999,110,$D$2:$D$999,400020,$H$2:$H$999,{4863,4007,301065,"CP0041",4764,2239,1791,301015,500103,"CP0021",4533,4537,4538,4539,4541,"SC0009",301145,301288}))<ROWS($N$6:$N6),"",(INDEX($I$2:$I$999,SMALL(IF(($C$2:$C$999=110)+($D$2:$D$999=400020)+($H$2:$H$999={4863,4007,301065,"CP0041",4764,2239,1791,301015,500103,"CP0021",4533,4537,4538,4539,4541,"SC0009",301145,301288})=3,ROW($C$2:$C$999)-1),ROW(A1)))))

I haven't checked thoroughly.

{array formula needs to be entered with
a key combination of Ctrl+Shift+Enter}

Regards,

 

[Clint88]

Khalid, that works perfectly! Thank you!!

Two quick questions regarding your formula.

1) Does your version not cause a problem because you've limited the array to fixed points rather than an entire column?

2) What is the function of the -1 after ROW($C$2:$C$999)?

 

[Clint88]

Mike,

Sorry, I didn't see your post at first. Let me try your suggestion as well!

 

[Khalid NGO]

Hi Clint,
Thanks for the feedback.

1) Does your version not cause a problem because you've limited the array to fixed points rather than an entire column?

It is recommended not to use full column range with array formulas. If you want to extend the range, just change all 999s with any number say 9999.

2) What is the function of the -1 after ROW($C$2:$C$999)?

Lets take an example with smaller data:

ROW(A2:A9) will generate an array of {2;3;4;5;6;7;8;9}
The minimum number which is required for the SMALL function is 2 here, whereas we need 1.
So the -1 will subtract 1 from each number and the new array would be {1;2;3;4;5;6;7;8} with smallest number of 1.

One more thing you may have noticed, if not I am including
You can see I have wrapped your COUNTIFS function with SUM, as normally COUNTIFS works on AND criteria not the OR criteria, unless we wrap it with SUM.

Hope it helped.

Regards,

 

[Clint88]

Thank you for that insight, Khalid!

Hopefully, this is the last follow-up question that I need.

If I want to make another one of those search criteria a multiple choice do I just add the {} with the choices separated by commas?

E.g. Instead of
=IF(SUM(COUNTIFS($C$2:$C$999,110,$D$2:$D$999,400020,...
...(INDEX($I$2:$I$999,SMALL(IF(($C$2:$C$999=110)+(...


Could I do
=IF(SUM(COUNTIFS($C$2:$C$999,{120,121,122},$D$2:$D$999,400020,...
...(INDEX($I$2:$I$999,SMALL(IF(($C$2:$C$999={120,121,122})+(...

 

[Khalid NGO]

Hi Clint,

Yes you can do this way (I guess).

Regards,