Couting a string in multiple colums while ignoring duplicates?

[jcolliu]

Hi, having alot of trouble with this one. I need to count a string of text, lets say "VP" in two columns, but ignore a duplicate if it shows up in the same row. I need "cat" to be found within any other strings, but the asterisk rule doesn't seem to work in SUMPRODUCT. What makes it even more challenging is I'm using INDEX and MATCH to find the correct columns as the data may be entered in the master sheet in a different order.

For example, here's what I have for it to find my string in one column:

=COUNTIF(INDEX('Custom Source'!A2:P900,0,MATCH("Manager Review",'Custom Source'!1:1,0)),"*"&"VP"&"*")

What would I do if I wanted to check another column that I also had to index match but keep it from counting dupes in the same row?

Thanks!

 

[Aladin Akyurek]

Right, but I'm also trying to find that "TL" value within all strings in it's own INDEX MATCHed column. The formula you provided works perfect when I can input "TL" exactly in that defined range, but I'm trying to do the following, which is returning a 0 -
...

Try to complete your earlier incomplete specs now in words...

 

[jcolliu]

I'm totally stumped, I don't see how this isn't working...

 

[Aladin Akyurek]

I'm totally stumped, I don't see how this isn't working...

I'm asking what the additional term must be. Would you word that?

 

[jcolliu]

The additional term must be "TL" found within any given string in a column with the heading "Manager Review" found in a given range. i.e. "TL/VP" must also be picked up. The "Manager Review" column may change it's placement over time as data will be copied and pasted from a master sheet with more or less variables that may shift the column left or right, hence the INDEX MATCH.

Basically what this formula is saying - of those matches that return "Mercer" (from the previous formula) how many have been reviewed by a TL?

 

[Aladin Akyurek]

The additional term must be "TL" found within any given string in a column with the heading "Manager Review" found in a given range. i.e. "TL/VP" must also be picked up. The "Manager Review" column may change it's placement over time as data will be copied and pasted from a master sheet with more or less variables that may shift the column left or right, hence the INDEX MATCH.

Basically what this formula is saying - of those matches that return "Mercer" (from the previous formula) how many have been reviewed by a TL?

Could you create a scaled-down sample of 5 rows X 4 columns?

 

[jcolliu]

Business Name	Description	Manager Review	Rating
other	status		A9
mercer	for mercer	TL	B7
mercer	update	TL/VP	C4
philadelphia	mercer		E7

<TBODY>
</TBODY>

Like I mentioned, there may be more or less columns depending on how many variables we add to the table, users will copy and paste to the sheet from a master source of data that may jumble the positioning of the columns, hence the use of INDEX MATCH to find the correct column. The rating column in the example above is not used. In this case,

=SUMPRODUCT(
ISNUMBER(SEARCH("mercer",INDEX('Custom Source'!A2:P900,0,
MATCH("Description",'Custom Source'!1:1,0))&"|" &
INDEX('Custom Source'!A2:P900,0,
MATCH("Business Name",'Custom Source'!1:1,0))))+0)

Returns the correct value of 3. Takes into account the fact that it finds mercer twice in the same row and doesn't double count. Perfect.

I need the next formula to add another condition to the previous one to find all those that contain mercer and ALSO have the string "TL" included in the "Manager Review" column. In this case, the formula should return a value of 2.

Let me know if I need to expand on anything, I know it's kind of confusing.

 

[Aladin Akyurek]

Business Name	Description	Manager Review	Rating
other	status		A9
mercer	for mercer	TL	B7
mercer	update	TL/VP	C4
philadelphia	mercer		E7

<TBODY>
</TBODY>

Like I mentioned, there may be more or less columns depending on how many variables we add to the table, users will copy and paste to the sheet from a master source of data that may jumble the positioning of the columns, hence the use of INDEX MATCH to find the correct column. The rating column in the example above is not used. In this case,

=SUMPRODUCT(
ISNUMBER(SEARCH("mercer",INDEX('Custom Source'!A2:P900,0,
MATCH("Description",'Custom Source'!1:1,0))&"|" &
INDEX('Custom Source'!A2:P900,0,
MATCH("Business Name",'Custom Source'!1:1,0))))+0)

Returns the correct value of 3. Takes into account the fact that it finds mercer twice in the same row and doesn't double count. Perfect.

I need the next formula to add another condition to the previous one to find all those that contain mercer and ALSO have the string "TL" included in the "Manager Review" column. In this case, the formula should return a value of 2.

Let me know if I need to expand on anything, I know it's kind of confusing.

Try...

=SUMPRODUCT(
  ISNUMBER(SEARCH("mercer",INDEX('Custom Source'!A2:P900,0,
   MATCH("Description",'Custom Source'!1:1,0))&"|" &
   INDEX('Custom Source'!A2:P900,0,
   MATCH("Business Name",'Custom Source'!1:1,0))))+0,
  ISNUMBER(SEARCH("TL",INDEX('Custom Source'!A2:P900,0,
   MATCH("Manager Review",'Custom Source'!1:1,0))))+0)

If you would want to name 'Custom Source!$A$1:$P$900 as Bdata, the foregoing formula can be made a bit better readable.

 

[jcolliu]

thank you so much. I had the same formula but must have had the parentheses messed up somehow. you've saved me a load of headache!

 

[Aladin Akyurek]

thank you so much. I had the same formula but must have had the parentheses messed up somehow. you've saved me a load of headache!

You are welcome.