PDA

View Full Version : [SOLVED] Help with sumproduct function



Digita
05-11-2009, 06:40 PM
Hi guys,

I need some help from formula experts and attach a small sample for your reference.

The dates when each order is received and when it is completed and despatched are logged in column A & B respectively.

I want to count the number of outstanding orders in each day. Any orders which are completed on same date are excluded. My sumproduct formula in column F needs to be modified to include items without a completion date in column B as well.

Thanks in advance.

Regards


kp

Digita
05-11-2009, 07:11 PM
Guys,

I think I got this problem sorted. In cell F2, I have:


=SUMPRODUCT(--(A$2:A$629<=E2),--(B$2:B$629>E2))+SUMPRODUCT(--(B$2:B$629<=E2),--(B$2:B$629=0))

Regards


kp

Bob Phillips
05-12-2009, 01:02 AM
You could combine them


=SUMPRODUCT(((A$2:A$629<=E2)*(B$2:B$629>E2))
+((B$2:B$629<=E2)*(B$2:B$629=0))

Digita
05-12-2009, 05:20 PM
Wow. Never thought it's possible. Thanks Bob.