Problems with Array Formula - 2 data criteria
 

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!

Problems with Array Formula - 2 data criteria
 

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!

Problems with Array Formula - 2 data criteria
 

Try this:

=SUM(N(FREQUENCY(IF((Price=0)*(Sale="NO"),MATCH(Or der,Order,)),MATCH(Order,Order,))0))

ctrl+shift+enter, not just enter


"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!