Count the numeric unique values based on criteria

[Villalobos]

Hello,

Could somebody help me how to count only the numeric unique Ship-to Parties based on criteria?

A1 (Criteria): 8000430
A2 (Expected result): 2

B1 Material C1 Ship-to Party
8000430 11267004
8000430 11269002
8000430 #
8000430 11269002
8000431 11267030

Thanks in advance!

 

[David Evans]

Create a simple example file and upload it - based on what you've written above, A2 would be 4, unless you're looking for 8000430 and 11269002 ...

You'll also get a quicker answer as the Ninjas don't have to create a file ... they're inherently lazy

 

[David Evans]

Here's a quick and dirty file where the criteria is 8000430 - if you need additional criteria advise - I'm rushing to a meeting, but someone else will be along to help, I'm sure

 

[David Evans]

I arrived home a re-visited your question.
Here's my final answer - your criteria of 8000430 confused me

 

[Villalobos]

Hi David,

Thank you response, I used a similar formula than you in your second file, just the prodlem is that for me the formula take into account the "#". This is the reason why the expected result is 2.

 

[Nebu]

Hi:

Try
=SUMPRODUCT(--(VillaData[[Material ]]=$B$1)*ISNUMBER(VillaData[Ship-to-Party]))

Thanks

 

[Villalobos]

Hi Nebu,

I checked your proposal but the result is 3 instead of 2.

 

[Nebu]

Hi:

Please find the attached . I had to use helper column.

Thanks

 

[Villalobos]

It is ok for me, thanks the help.
But is there another way to get the result without helper column?

 

[Jake Collins]

It is ok for me, thanks the help.
But is there another way to get the result without helper column?

Dear Villalobos

I believe the following array formula does what you need.

=SUM(IF(FREQUENCY(MATCH(VillaData[Ship-to-Party],VillaData[Ship-to-Party],0),MATCH(VillaData[Ship-to-Party],VillaData[Ship-to-Party],0))<>0,1*ISNUMBER(VillaData[Ship-to-Party])*(VillaData[[Material ]]=$B$1),0))

 

[Villalobos]

Yes, this is perfect.