#NA error

[Scott@CW]

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.

 

[bpeltzer]

Each array argument of the sumproduct function must have the same number of
elements. But you've got some that reference rows 22 through 700 and others
that reference 22 through 695. Try
=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=$J12))

 

[Ragdyer]

First thing I would do would be to *equalize* all the range references!

Your last 2 don't end at row 700 like the first 5 do.

 

[Peo Sjoblom]

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

 

[Scott@CW]

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.

 

[Scott@CW]

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.

 

[PCLIVE]

You may need to make sure you have rows that match your criteria. Oh wait.
It looks like you moved your last criteria from "J12" to "J13". See if
that's it. It works for me.

HTH,
Paul

 

[sahafi]

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.