Hi

I assume you have headings in Row1. Use a helper column, say column D
(the column can be hidden if desired) and insert this formula in D2:
=A2=A1 and copy the formula down your data range. Then use this
formula:

=SUMPRODUCT(--(D2:D100=FALSE),--(B2:B100=0),--(C2:C100="NO"))

Hopes this helps.

---
Per

On 2 Maj, 02:48, lisann wrote:
I have been searching forums and trying formulas for 6 hours and I still
can't make this work. *

I have this table (below) and I want to know how many orders were no cost
and indirect (this dataset has 2). *I know it's simple, BUT I also have rows
with duplicate order numbers and I ony want to count each order once.

Is there a formula/ array that will count when unit price = $0 and direct
sale = NO AND only count the the row once if the order number is a duplicate?

I know I can do a unique filter and then apply a simple formula, but I'm
designing a workbook that will import the data from an external source into *
"report" worksheet. *I need to write the formulas on a Summary worksheet so
as not to disturb the original data content. *Also, my executives do not want
to see results from manipulated data or mannually counted and keyed.

Here is the data set I'm playing with. *[A] is Order#, [b] is Price, and [C]
is type of sale.

[A] * * *[b] * *[C]
1468 * * $995.00 * * * *YES
1468 * * $- * * YES
1473 * * $- * * YES
1491 * * $419.54 * * * *NO
1491 * * $703.13 * * * *NO
1498 * * $- * * YES
1501 * * $- * * NO
1508 * * $- * * YES
1511 * * $- * * NO
1511 * * $- * * NO
1531 * * $994.60 * * * *YES

I tried something like this and all I get is a #NA error.

=SUM((IF(FREQUENCY(A2:A12,A2:A12)0,1,0))*(IF((H2: H120)*(I2:I12="NA"),1,0)*))

Please help!