Help Plz

C

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.
 
C

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.
 
S

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?
 
C

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.
 
S

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?
 
C

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
 
S

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top