Sumproduct formula is returning #value error

Hello,

How do I correct the below formula to not return a #value error? The below formula is vlooking up a value in spreadsheet "Tech Level"- column A to retrun the following values in column BE on the Tech Level sheet. Thanks.

=SUMPRODUCT('Tech Level'!$A$2:$A$2000,A9)*('Tech Level'!$BE$2:$BE$2000>=21)*('Tech Level'!$BE$2:$BE$2000<=24)

Hello Smith,

In the formula some parentheses are missing also should be =A9 NOT ,A9. try this

=SUMPRODUCT(('Tech Level'!$A$2:$A$2000=A9)*('Tech Level'!$BE$2:$BE$2000>=21)*('Tech Level'!$BE$2:$BE$2000<=24))

Since you are on Excel 2007, better to use COUNTIFS.

=COUNTIFS('Tech Level'!$A$2:$A$2000,A9,'Tech Level'!$BE$2:$BE$2000,">=21",'Tech Level'!$BE$2:$BE$2000,"<=24")