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.

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))


"Scott@CW" wrote:

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.

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"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.

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.

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.

"bpeltzer" wrote:

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))


"Scott@CW" wrote:

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.

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.

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


"Scott@CW" wrote in message
...
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.

"bpeltzer" wrote:

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))


"Scott@CW" wrote:

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.

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.