Excel countin formula problem

Hi,
I have small problem with excel. As you will see below I coped part of my dates from excel.

I Item
II SAP part number
III SNR
IV Date card is taken from stock?
V Has unit been taken from buffer stock?
I II III IV V

64 3000233 RC3A59K291 31/08/2007 YES
65 3000233 RC3A58T0E3 31/08/2007 YES
66 3000229 RC3A59V17J
67 3000229 RC3A59V1G9

The problem is that I have to create formula in another excel sheet which count the qty of stock which left in stores. Every type of stock have different SAP number and it is in 1 excel sheet. The formula supposes to count the SAP Nr (for example 3000233) and if I complete column has unit been taken from buffer stock then this formula should automatically deduct the unit. It will be easy to create formula if will be 1 type of stock but in this case there is several types of stock.
I hope you have some idea how to do this.

Thanks in advance

Hi

How about creating an example workbook showing both sheets, and some sample data.

Show what you would expect to get for the sample and explain how that would have been obtained.

rylo

Try using a sumproduct formula:

- assuming your SAP number is in column B
- assuming your buffer stock status is in column E
- assuming these stock info is in workbook called "log"

- On a seperate worksheet, you will have a list of your SAP numbers. Assuming your SAP numbers is in column A

=SUMPRODUCT(([log.xls]Sheet1!$B$1:$B$10000=A1)*([log.xls]Sheet1!$E$1:$E$1000=""))

2 problems:
- SUMPRODUCT formula slows the worksheet down
- SUMPRODUCT must have a defined range set (i.e. instead of B:B)