• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

countif for distinct values....

dan_l

Active Member
Here's the problem:

I've got a list. There are two columns, one is states the other is transit routes. So:


[State] [Route]

Illinois A100

Indiana B50

Iowa A100

Illinois A100

Wisconsin A190

Illinois D35


What I need is to be able to specify a state and get the number of unique transit routes associated with that state. I've kind of got this working in a UDF, but it's a massive set of data and the performance is pretty abysmal.


Any thoughts? this feels like a sumproduct or array formula problem, I'm just not sure how to solve it.
 
Then add a helper column C, i assume states in col A, routes in column B.... Enter formula to count for repeated routes


countif($C$1:C1,C1) enter in c1 and drag down this will gives list of nos. Now use countifs:


countifs(A1:A10,STATE,C1:C10,1)


This will count for a state with route repeated only once,


regards,

Faseeh
 
No: The countif in the helper column doesn't account for the selection of the state being specified.


Hmmm.


but maybe you're ont he right track....
 
This would work for the helper column:


if(SUMPRODUCT((A:A="Illinois")*(B:B=B1))


I would have to put an if to resolve it as 0 or 1 though.


But I think there must be a better way.
 
Hi dan_l,


Kindly check out this file, i hope it will work

http://dl.dropbox.com/u/60644346/dan_l_Staes%20and%20Route%20Solved.xlsx


Regards,

Faseeh
 
Previously solved here:

http://chandoo.org/forums/topic/combine-countifs-and-unique-function
 
Back
Top