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!

 

[jcolliu]

Oops, didn't mean "cat" in the second line, meant "VP"

 

[Aladin Akyurek]

Oops, didn't mean "cat" in the second line, meant "VP"

I keep saying this all the time. Use words, not formulas, to describe a problem for which you need a solution. That said, is it?

=SUMPRODUCT(ISNUMBER(SEARCH("VP",RangeX&"|"RangeY))+0)

 

[jcolliu]

I keep saying this all the time. Use words, not formulas, to describe a problem for which you need a solution. That said, is it?

=SUMPRODUCT(ISNUMBER(SEARCH("VP",RangeX&"|"RangeY))+0)

I keep saying this all the time. Use words, not formulas, to describe a problem for which you need a solution. That said, is it?

=SUMPRODUCT(ISNUMBER(SEARCH("VP",RangeX&"|"RangeY))+0)

That's working great when I can directly input the cell ranges, but I'm trying to use columns that are found through INDEX and MATCH, i.e here are the formulas to find the columns I'll have to find the "VP" value in:

(INDEX('Custom Source'!A2:P900,0,MATCH("Manager Review",'Custom Source'!1:1,0)
(INDEX('Custom Source'!A2:P900,0,MATCH("Description",'Custom Source'!1:1,0)


When I insert the above formulas into the Range X & Range Y, something's not clicking. I'll keep trying, maybe my syntax is off.

 

[jcolliu]

Nvm, bad syntax. Thank you! Here's the formula that worked if anyone's interested

=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)

 

[jcolliu]

What does the "|" do exactly?

 

[Aladin Akyurek]

Nvm, bad syntax. Thank you! Here's the formula that worked if anyone's interested

=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)

That's right.

What does the "|" do exactly?

Used to discriminate the two ranges clearly.

A2: It starts with V
B2: Product
A3: VP
B3: Department

While A3&B3 would yield a correct match, so would A2&B2 mistakenly.

The foregoing won't happen with: A2&"|"&B2 and A3&"|"&B3.

 

[jcolliu]

thanks for the explanation.

Any way to add one more condition to that formula? Like say, if it finds "mercer" in either of the two colums, count each instance but don't double count if it's found in the same row, as the formula in my last post shows (and it works great, btw!)

BUT ONLY COUNT IF

it finds some other value, say "TL" in another specified column?

 

[Aladin Akyurek]

thanks for the explanation.

Any way to add one more condition to that formula? Like say, if it finds "mercer" in either of the two colums, count each instance but don't double count if it's found in the same row, as the formula in my last post shows (and it works great, btw!)

BUT ONLY COUNT IF

it finds some other value, say "TL" in another specified column?

Do you mean another conditional term to the current formula? If so, as example:

=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,
    (Q2:Q900="TL")+0)

This adds a term with an equality test involving a different range.

 

[jcolliu]

Do you mean another conditional term to the current formula?
This adds a term with an equality test involving a different range.

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 -

=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)))

Will continue to work on it, I really appreciate all your help!