Identify distinct values based upon multiple criteria

[spydey]

I am trying to use a formula to obtain a count of the number of distinct values in a list of data I have.

I can use the follow:

=sum(1/countif(ID_Range,ID_Range))

And it returns the correct number of distinct IDs in the range.

However, I need to expand my number of criteria to something such as:

=sum(1/countifs(Item_Range,$A2,Location_Range,$B2,ID_Range,ID_Range)

I am getting decimal points instead of whole numbers ..... throwing me off a bit.

Thanks for your help!

-Spydey

 

[Marcelo Branco]

Could you provide a small data sample, criteria and expected results?

M.

 

[spydey]

Could you provide a small data sample, criteria and expected results?

M.

Actually, I think I got it. I had to approach it from a different angle. I was on the right path but just needed to tweak and adjust a few things.

My ending formula is:

=sum(if($A2=Item_Range,1/(countifs(Item_Range,$A2,Location_Range,$B2,ID_Range,ID_Range)),0))

confirmed with CSE.

Looks to be working correctly.

Thanks though Marcelo!

Cheers!

-Spydey

 

[Marcelo Branco]

Great you solved your problem!

I was thinking in a better (much faster) formula using the FREQUENCY function. You can find in this forum many threads that use this kind of formula to get a unique (distinct) count .

M.

 

[spydey]

I will have to search around for that. I have noticed that it does take it a moment or more to provide the result. Most likely Frequency would be faster. Thanks for the tip!

-Spydey

 

[Marcelo Branco]

I will have to search around for that. I have noticed that it does take it a moment or more to provide the result. Most likely Frequency would be faster. Thanks for the tip!

-Spydey

With a small sample of data, and criteria, i can show you an example of such formulas.

M.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(ID_Range=""),IF($A2=Item_Range,IF($B2=Location_Range,MATCH(ID_Range,ID_Range,0)))),ROW(ID_Range)-ROW(INDEX(ID_Range,1,1))+1),1))

would be faster thann:

=sum(if($A2=Item_Range,1/(countifs(Item_Range,$A2,Location_Range,$B2,ID_Range,ID_Range)),0))

 

[spydey]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(ID_Range=""),IF($A2=Item_Range,IF($B2=Location_Range,MATCH(ID_Range,ID_Range,0)))),ROW(ID_Range)-ROW(INDEX(ID_Range,1,1))+1),1))

would be faster thann:

=sum(if($A2=Item_Range,1/(countifs(Item_Range,$A2,Location_Range,$B2,ID_Range,ID_Range)),0))

So I ended up using what you provided above, and changed a few things. It works wonderfully! Thank you so much for your assistance.

-Spydey

 

[Aladin Akyurek]

So I ended up using what you provided above, and changed a few things. It works wonderfully! Thank you so much for your assistance.

-Spydey

You are welcome. Thanks for the update.