You still don't need the -- or the parentheses around that last portion
(H2:H15195).

I'd try that first.

Then if that doesn't clear all the problem, look for errors in any of those
ranges in your formulas. Remember to look in hidden rows, too!


Sarah (OGI) wrote:

Sorry guys, thanks for your help so far, but I've since changed some of the
details on my workbook, so here is how the situation is now. I'm now getting
a result of #Value!

I have 2 worksheets.

Sheet 4 contains info in the following order:
Customer Ref (integer)/Customer Name/Start Date (dd/mm/yyyy format)/Start
Date minus 1 year

Sheet 2 contains info in the following order:
Customer Ref (integer)/Lookup Code/Date (dd/mm/yyyy format)/Class/Company
Name/Product/Company Code/Amount

I have entered the following formula in Sheet 4 to find the customer ref in
Sheet 2 and to sum all the relevant amounts that are within the specified
date range, i.e. 01/09/2005 in D2 and 01/09/2006 in C2:
=SUMPRODUCT(--(Sheet2!A2:A15195=A2),--(Sheet2!C2:C15195=D2),--(Sheet2!C2:C15195<C2),--(Sheet2!H2:H15195))

Any ideas?

"Sarah (OGI)" wrote:

I have entered the following expression:
=SUMPRODUCT(--(Sheet2!B:B=D2),--(Sheet2!B:B<C2),--(Sheet2!H:H))

D2 shows 01/09/2005 and C2 shows 01/09/2006.

I have some data on Sheet 2 with data listed as follows:
Date/Class Code/Company Name/Amount

I would like to sum the amounts where the dates are between D2 and C2.

I'm getting a result of #NUM!

Any suggestions? I'm not sure I'm using the right function, but I'm asking
for multiple criteria, i.e. where the date is = one date but < another date.

--

Dave Peterson