Help Plz

[Chad Portman]

I am having issues with the following formula:

=SUMPRODUCT(('MCO '!$D$2:$D$3000=B$3)*('MCO '!$E$2:$E$3000=$A7))

But if I run this formula and say the data stops at row 600. It changes the
fomula to the following:

=SUMPRODUCT(('MCO '!$D$2:$D$600=B$3)*('MCO '!$E$2:$E$600=$A7))

I thought about trying to using indirect to fix this but can not make it
work any ideas.

 

[Chad Portman]

or a way to look down that same range for just the none blank cells. I just
need to make sure it looks down the whole range of row 2 to row 3000 for all
non blank cells that might make this easier. or not.

 

[Sheeloo]

What do you mean by " if I run this formula and say the data stops at row 600"?

Where does the formula change? Formula should remain as you typed it
irrespective of the last row containing data...

What is the Excel version?

 

[Chad Portman]

Excel 2003 and we I say run the formula I mean press F9 to calculate. I
understand that it should not cahnge but it does and I need it not too that
is why I am confused and am here asking for help.

 

[Sheeloo]

It is not changing for me... hence the question...

Try to define a NAME (Insert->Name...) for the range $D$2:$D$3000 and
another for $E$2:$E$3000 and use that...
Also see if the name also changes...

If it does then can you share the file?

 

[Chad Portman]

not able to share the file. classified info. (sorry) I tried using named
ranges but when doing that I get the error of unable to read file. Which
reading online indicates could be due to named ranges from sheets that are
not the same sheet as the named range

 

[Sheeloo]

np.

define the name at workbook level then it will work.

 

[Chad Portman]

how do you do that I just learned about naming ranges

 

[Sheeloo]

See what the name refers to
I defined to names... one called ColD referring to
='MCO '!$D$2:$D$300
the other ColE to
='MCO '!$E$2:$E$3000


then used the formula
=SUMPRODUCT((ColD=B$3)*(ColE=$A7))