Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems when sorting data containing array formulas | Excel Worksheet Functions | |||
Array formula - sum with multiple criteria | Excel Discussion (Misc queries) | |||
Problems changing array field in formula | Excel Worksheet Functions | |||
Array Formula : Problems with sharing workbook | Excel Discussion (Misc queries) | |||
Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it ! | Excel Discussion (Misc queries) |