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...

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...

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...

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...

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...

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...