Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different comparison criteria in sumproduct
I'm looking for a formula that returns number of items delivered on time. Ex: How many items "Apple" were delivered on time in the table below. A B C Item Requested Delivered ==== ========= ========= 1 Apple 2/1/07 3/1/07 2 Apple 2/5/07 2/5/07 3 Orange 2/3/07 4/1/07 4 Apple 2/3/07 2/2/07 5 Apple 2/15/07 2/20/07 sumproduct(($A1:$5="Apple")*($B$1:$B$5=$C$1:$C$5)) doesn't work... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different comparison criteria in sumproduct
=SUMPRODUCT(--(A1:A5="Apple"),--(B1:B5=C1:C5))
"adimar" wrote: I'm looking for a formula that returns number of items delivered on time. Ex: How many items "Apple" were delivered on time in the table below. A B C Item Requested Delivered ==== ========= ========= 1 Apple 2/1/07 3/1/07 2 Apple 2/5/07 2/5/07 3 Orange 2/3/07 4/1/07 4 Apple 2/3/07 2/2/07 5 Apple 2/15/07 2/20/07 sumproduct(($A1:$5="Apple")*($B$1:$B$5=$C$1:$C$5)) doesn't work... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different comparison criteria in sumproduct
adimar,
It looks like "($A1:$5="Apple")" is wrong. Shouldn't it be something like: ($A$1:$A$5="Apple") That formula will only count the items where Delivered = Requested. What if Delivered is before Requested? Do you want to count those too? =sumproduct(($A$1:$A$5="Apple")*($B$1:$B$5=$C$1:$ C$5)) HTH, Conan "adimar" wrote in message ... I'm looking for a formula that returns number of items delivered on time. Ex: How many items "Apple" were delivered on time in the table below. A B C Item Requested Delivered ==== ========= ========= 1 Apple 2/1/07 3/1/07 2 Apple 2/5/07 2/5/07 3 Orange 2/3/07 4/1/07 4 Apple 2/3/07 2/2/07 5 Apple 2/15/07 2/20/07 sumproduct(($A1:$5="Apple")*($B$1:$B$5=$C$1:$C$5)) doesn't work... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different comparison criteria in sumproduct
Having fixed as suggested, still doesn't work... Sample dates I'm actually comparing B C Displays as: 12/5/2007 12/18/2007 Value is: 12/5/2007 3:38:24 PM 12/18/2007 4:28:11 PM B<C, still sumproduct returns 0, that is "on time", but it was delivered late... Thank you. "Conan Kelly" wrote: adimar, It looks like "($A1:$5="Apple")" is wrong. Shouldn't it be something like: ($A$1:$A$5="Apple") That formula will only count the items where Delivered = Requested. What if Delivered is before Requested? Do you want to count those too? =sumproduct(($A$1:$A$5="Apple")*($B$1:$B$5=$C$1:$ C$5)) HTH, Conan "adimar" wrote in message ... I'm looking for a formula that returns number of items delivered on time. Ex: How many items "Apple" were delivered on time in the table below. A B C Item Requested Delivered ==== ========= ========= 1 Apple 2/1/07 3/1/07 2 Apple 2/5/07 2/5/07 3 Orange 2/3/07 4/1/07 4 Apple 2/3/07 2/2/07 5 Apple 2/15/07 2/20/07 sumproduct(($A1:$5="Apple")*($B$1:$B$5=$C$1:$C$5)) doesn't work... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different comparison criteria in sumproduct
I figured-out I had errors in Constant definitions elsewhere... comparing wrong values. Thank you, both. "adimar" wrote: I'm looking for a formula that returns number of items delivered on time. Ex: How many items "Apple" were delivered on time in the table below. A B C Item Requested Delivered ==== ========= ========= 1 Apple 2/1/07 3/1/07 2 Apple 2/5/07 2/5/07 3 Orange 2/3/07 4/1/07 4 Apple 2/3/07 2/2/07 5 Apple 2/15/07 2/20/07 sumproduct(($A1:$5="Apple")*($B$1:$B$5=$C$1:$C$5)) doesn't work... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different comparison criteria in sumproduct
B<C, still sumproduct returns 0, that is "on time", but it was delivered
late... ??? B<C ??? B less than C would be late, correct? BC B greater than C would be early, correct? B=C B equal to C would be on time, correct? Do you want to count all items that are either early or on time? I think this should work for you (make sure B=C...not B<C): =sumproduct(($A$1:$A$5="Apple")*($B$1:$B$5=$C$1:$ C$5)) If that is not doing what you expect, then I'm not quite sure what is going on. Please send more detailed information (copy-n-paste your formula) HTH, Conan "adimar" wrote in message ... Having fixed as suggested, still doesn't work... Sample dates I'm actually comparing B C Displays as: 12/5/2007 12/18/2007 Value is: 12/5/2007 3:38:24 PM 12/18/2007 4:28:11 PM B<C, still sumproduct returns 0, that is "on time", but it was delivered late... Thank you. "Conan Kelly" wrote: adimar, It looks like "($A1:$5="Apple")" is wrong. Shouldn't it be something like: ($A$1:$A$5="Apple") That formula will only count the items where Delivered = Requested. What if Delivered is before Requested? Do you want to count those too? =sumproduct(($A$1:$A$5="Apple")*($B$1:$B$5=$C$1:$ C$5)) HTH, Conan "adimar" wrote in message ... I'm looking for a formula that returns number of items delivered on time. Ex: How many items "Apple" were delivered on time in the table below. A B C Item Requested Delivered ==== ========= ========= 1 Apple 2/1/07 3/1/07 2 Apple 2/5/07 2/5/07 3 Orange 2/3/07 4/1/07 4 Apple 2/3/07 2/2/07 5 Apple 2/15/07 2/20/07 sumproduct(($A1:$5="Apple")*($B$1:$B$5=$C$1:$C$5)) doesn't work... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF, criteria is cell-to-cell comparison | Excel Worksheet Functions | |||
Sumproduct - two+ criteria | Excel Worksheet Functions | |||
SUMPRODUCT with 3 criteria | Excel Discussion (Misc queries) | |||
Sumproduct with 2 criteria | Excel Worksheet Functions | |||
Two cell Comparison with three different Criteria | Excel Worksheet Functions |