Excel Formula Help needed :((

[dakotauk]

Can anyone help with a excel formula, I need to calculate a formula that shows how many records are outstanding ie


A B C
1 PRODUCT DELIVERY DUE DELIVERED ON
2 DRINKS 10/12/06 10/12/06
3 FOOD 12/12/06 12/12/06
4 GIFTS 09/12/06
5 PAPER 08/12/06
6 STAPLES 08/12/06 12/12/06

Delivered = 3 Outstanding = ***** need to find

I would like to know a formula to calculate the amount of late deliveries on the example above, I have tried everything I can think of and just can’t find a formula to work.

C4 AND C5 ARE MISSING VAULES how can I count these to make a value of outstanding deliveries.

Any help would be greatfully recieved as its doing my head in

Gaz

 

[mintjulep]

how about =countblank(C2:C6)

 

[dakotauk]

tried that but new fields are added daily so amount fields is constantly changing, ie C3:C6 next day it maybe C3:C10 etc

 

[Fenrirshowl]

Gaz

Try COUNTA on the column which will always be complete and then deduct COUNTA on the column that will have spaces. I.e. COUNTA(A:A)-COUNTA(C:C)

Fen

 

[dakotauk]

didnt work keeps claiming 0 is outstanding

 

[Fenrirshowl]

Then the blank entries in C probably aren't blanks. Try Countif(B:B,">0")-Countif(C:C,">0") - works slightly differently and may do the trick.

 

[SkipVought]

Hi,
[tt]
=SUMPRODUCT((DELIVERED_ON=0)*(1))+SUMPRODUCT((DELIVERED_ON>DELIVERY_DUE)*(1))
[/tt]

Skip,

_[red][/red]

 

[dakotauk]

Thanks all

Gaz