Count unique values that have a condition

[flaviooooo]

OK, here is my problem:

I have a list of orders that were started in a certain period. Most of them have a produced qty, but some don't. In their cases, the order started and then they found out there was not enough material or whatever to go ahead.
It can also happen that an order was produced in 2 steps, both with a produced qty.

Now I want to have a formula that counts the unique orders that were actually produced. So filtering out the ones with quantity 0.

An example:

ORDER QTY
123 1000
124 1050
125 0
123 0
124 1000
126 2000

The result of the formula should be 3 (123, 124 and 126)

 

[Andrzejek]

If you have this data in Access table, you can run a query:

Select Distinct MyTable.ORDER 
From MyTable 
Where MyTable.QTY > 0

Have fun.

---- Andy

 

[flaviooooo]

Hey Andy, thanks for the reply

I forgot to mention, that it is an Excelsheet where I need to create the formula...

 

[GSCaupling]

Hi flaviooooo,

Use a PivotTable - make Order your row labels and filter Quantity for non-zero values. Add a simple =count formula and count the rows in the pivot table.

Not necessarily pretty, but it works.

Good luck!
GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]