SUMPRODUCT() worked then broke

[Ann Scharpf]

I have the following formula (actually many formulas like
this one) in my sheet:

=SUMPRODUCT(--(System="DIFMS-C"),--
(LaborType="MAINTENANCE"),--(UserID=$A5),Reg)

System, LaborType, UserID and Reg are all named ranges on
a "raw data" sheet. Turns out that someone gave me the
wrong data sheet for the raw data.

So, I imported the new sheet. Deleted the named ranges on
the original sheet and assigned them on the replacement
sheet. Now ALL my formulas are giving the result #NUM.

Another SUMIF() formula that is only referencing the Reg
range is working fine. I did not touch the worksheet that
has the SUMPRODUCT() functions.

Can anyone give me an idea on where to look for solving
this problem? This project is due today. OF COURSE!

Thanks for your help.

Ann Scharpf

 

[Peo Sjoblom]

All arrays most have the same sized ranges, so most likely when you created
the new ranges you did
not use the same size i.e. if System = A2:A250 then the rest must have the
same numbers of rows
e.g. Reg might be E2:E250 but not E2:E252

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Ann Scharpf]

Peo:

When I named the new ranges, I tried two methods:

1. Selected entire column for each range.
2. Selected rows 1:3440 in the column for each range.

Neither of these worked.

Ann

 

[Juan Sanchez]

Two Questions...

Does the formulae were the error is occurring shows #REF!
instead of the names...?

Do you still have the original sheet with the SUMPRODUCT
functions working on bad raw data?

Cheers
Juan

 

[Ann Scharpf]

OK, my bad! I realized that when I tried the rows 1:3440
method, I missed the Reg range. And I think I remember
that SUMPRODUCT() does not like using whole columns for
range names, so that's why that method failed.

Thanks, Peo, for the answer. You were right on the money.

And thanks, Juan, for trying to help out, too

Ann

 

[Don Guillett]

try naming your ranges with defined names to use the one you know will
always be the longest
rngA
=offset($a$1,0,0,counta($a:$a),1)
and
rngB using the counta from rngA
=offset($b$1,0,0,counta($a:$a),1)