Couting a string in multiple colums while ignoring duplicates?

jcolliu

New Member
Joined
Aug 27, 2014
Messages
19
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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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)
 
Upvote 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)


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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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:
Rich (BB code):
=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.
 
Upvote 0
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 -

Code:
=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!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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
Back
Top