filter with multiple values

[zaveed]

Hi,

I have an excel sheet with 2 columns.

Column A has the name and column B has the branch.

For one Name i want to check in column B if it has any of these 3 values which start with "mas" or "rel" or "dev" , if it does not then it should return a value zero in column C. if it has only one value out of the 3 then it should return 1 similarly for 2 and 3.

for ex : in the attached sheet from Column A
"Article" has all the 3 values it should show up 3
"Cleanser" has only 2 values that start with either "mas" or "dev" or "rel" , it should show up 2
"Claims" does not have any of the 3 values , it should show up as 0.

with the pivot table i can group the values in Column A but cannot filter for 3 values in Column B.

attached is the sample data.

Appreciate the help.

[Glenn Kennedy]

One way, with an array formula:

=MIN(3,SUMPRODUCT(($A$2:$A$40=A2)*(IFERROR(SEARCH(TRANSPOSE($F$2:$F$4),$B$2:$B$40),0))))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[zaveed]

One way, with an array formula:

=MIN(3,SUMPRODUCT(($A$2:$A$40=A2)*(IFERROR(SEARCH(TRANSPOSE($F$2:$F$4),$B$2:$B$40),0))))

Don't type the curly brackets yourself - it won't work...

Hi Glenn,

Thank you for the formula, it seems to work for some cases and for some it is showing the incorrect count. for one particular name that i checked it is showing the count as 3 where as it should be 2 .

I have modified the formula to change the row numbers to 7000 as it has data till that row.

How ever it seems to work if i copy the data to another sheet with less rows, is the max number of rows an issue here?

Attachment 645097

[menem]

Is 'master' is counted as 'mas' ?

=SUM(COUNTIFS(A:A,A2,B:B,{"mas*","rel*","dev*"}))

It's seem my formula is include 'master' in counting as well.

Regards.

[Glenn Kennedy]

The problem my formula had was that it was counting mas in the middle of a term. This will count it ONLY at the start:

=SUMPRODUCT(($A$2:$A$40=$A2)*(IFERROR((SEARCH(TRANSPOSE($F$2:$F$4),$B$2:$B$40)=1),0)))

memem's is much simpler, but DO NOT use whole column ranges. It will be slow.

in both cases, if you want the maximum value returned to be 3

=MIN(3,formula_you_choose)

[Glenn Kennedy]

or is it more complicated than that??

Do you want 1 or 2 for:

XXX dev
XXX blah
XXX dev

ie only 1 dev gets counted???

[zaveed]

Apologies Glenn for the delay in reply and thank you for the other formula.

or is it more complicated than that??

Do you want 1 or 2 for:

XXX dev
XXX blah
XXX dev

ie only 1 dev gets counted???

no this is not require because of the below reason.

The problem my formula had was that it was counting mas in the middle of a term. This will count it ONLY at the start:

=SUMPRODUCT(($A$2:$A$40=$A2)*(IFERROR((SEARCH(TRANSPOSE($F$2:$F$4),$B$2:$B$40)=1),0)))

The above formula works fine but might need some tweaking. right now it will count if column b has either "dev" or "develop". I want the formula to count when it matches the exact string i.e "dev" if there is letter or a number or a underscore or anything after that it should ignore ex :dev25_ten or dev7685 or release_checking, master_test . It should not not count these values. Count it only when the value is either "mas" or "rel" or dev".

Tried to use Exact function but was not able to get it to work.

Thank you for the help!

[Glenn Kennedy]

Use this, array entered:

=SUMPRODUCT(($A$2:$A$40=$A2)*(IFERROR((SEARCH(TRANSPOSE($F$2:$F$4&" "),$B$2:$B$40&" ")=1),0)))

[zaveed]

Is 'master' is counted as 'mas' ?

=SUM(COUNTIFS(A:A,A2,B:B,{"mas*","rel*","dev*"}))

It's seem my formula is include 'master' in counting as well.

Regards.

Use this, array entered:

=SUMPRODUCT(($A$2:$A$40=$A2)*(IFERROR((SEARCH(TRANSPOSE($F$2:$F$4&" "),$B$2:$B$40&" ")=1),0)))

Thank you Glenn and menem!