Hi there, I need some help with counting unique values if two other values match using vLookup or if statement.
I have two worksheets. (See attached example).
In column titled “No of Suppliers” in the worksheet “Lookup”, I need to count the number of unique values from column “Suppliers” of worksheet “Data” if Destination City in “Lookup” matches with Destination City in
“Data”.
I have done this manually in the attached example.
Hope this makes sense and someone can help me please.
gsrai31
You can use this formula in B2 copied down
=SUM(IF(FREQUENCY(IF(Data!A$2:A$10=A2,MATCH( Data!B$2:B$10,Data!B$2:B$10,0)),ROW(Data!B$2:B$10)-ROW(Data!B$2)+1),1))
Note: this is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar
Excellent!! Many thanks for your help.
Originally Posted by gsrai31
Bkwaass, it doesnt work
Given gsrai31's response to my suggestion I suspect it does work - why do you say it doesn't?
Audere est facere
Reddy
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Bookmarks