Make sure your lookup values and your ranges are of the same data type. The
-- sometimes will not have effect on data that already keyed in. Try typing
over the same values on all 7 columns as well as on your result sheet. Test
just one row and see if that helps.


--
If u change the way u look @ things, the things u look at change.


"Scott@CW" wrote:

I did what you suggested and it changed the error to 0. However my test data
should return a value of 4. I also tried the below formula and am still
getting 0.

=SUMPRODUCT(--($E$22:$E$700=$K$1),--($F$22:$F$700=$K$2),--($G$22:$G$700=$K$3),--($H$22:$H$700=$K$4),--($I$22:$I$700=$K$5),--($J$22:$J$700=$K$6),--(K$22:K$700=$J13))

I had this sheet working with just two variable I need to push it up to 6 or
7 for a new report.


"Peo Sjoblom" wrote:

The ranges need to be of equal size, the last 2 end at row 695 and not 700
like the other


--
Regards,

Peo Sjoblom


"Scott@CW" wrote in message
...
I cant figure out why I am getting the #NA error with the below formula.
There is data to match the critera in the formula range.

=SUMPRODUCT(($E$22:$E$700=$K$1)*($F$22:$F$700=$K$2 )*($G$22:$G$700=$K$3)*($H$22:$H$700=$K$4)*($I$22:$ I$700=$K$5)*($J$22:$J$695=$K$6)*(K$22:K$695=$J12))

K1 through K6 are all referencing a Data validation list on another page.

Please help.