how to use sumproduct count a date range & exclude 7 different words from the range

I reckon
=sumproduct((a1:a100>=date(2012,4,30))*(a1:a100< =date(2012,5,4))*(b1:b100<>{"TM","TD","GM","DV","RR","MR","AC"}))

I should also say that I am working with excel 2003.

Hi JosephP,

Thank you for your quick response :-). Excel said the formula contained an error. Excel sugggested that I fix the error like this:
=SUMPRODUCT((A1:A100>=DATE(2012,4,30))*(A1:A100<DATE(2012,5,4))*(B1:B20{TM","TD","GM","DV","RR","MR","AC"}))


However, the answer it is coming up with is 0, and the answer should be 13.

Any ideas?

can you post a workbook-the forum seems to be changing the formulas posted as yours is invalid syntax

Try:

=SUMPRODUCT((A2:A100>=DATE(2012,4,30))*(A2:A100<DATE(2012,5,4))*(ISNA(MATCH(B2:B100,{"TM","TD","GM","DV","RR","MR","AC"},0))))

Note: Make sure not to include column headers or text in the Date column or you will get #VALUE! error.

Thanks for the 2nd way :-) And definitely thanks for the tip on not including column headers with the date range. I did not know that. I am very new to using these formulas. I appreciate your help!

Sorry, I was a little misleading.. if you had included a column to sum up, then the data shouldn't include the headers...or you will get the errors....

if you use the comma syntax instead of multiplication for the data range (assuming you can), then text values won't be a problem