Sumproduct

I need help with my Sumproduct. It works fine until a new file is imported and then the Range changes resulting in the wrong answer being returned.

My sumproduct is:

=SUMPRODUCT(('Management_ PM'!$V$3:$V$30000="0-3")*--('Management_PM'!$X$3:$X$30000))

If the new file has say 10000 rows the formula changes to
=SUMPRODUCT(('Management_ PM'!$V$3:$V$10000="0-3")*--('Management_PM'!$X$3:$X$10000))

if subsequent files come in with more than 10k it does not increase.


Is there a better way of doing this that will not change the formula when a new file is imported.

Thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
hi,

pls try

=SUMPRODUCT((INDIRECT("Management_PM!"&ADDRESS(3;22)&":"&ADDRESS(30000;22))="0-3")*--(INDIRECT("Management_PM!"&ADDRESS(3;24)&":"&ADDRESS(30000;24))))

Open in new window

Regards
0
JagwarmanAuthor Commented:
Unfortunately it returns an error and highlights 3:22
0
Rgonzo1971Commented:
Hi,
it is because of the localization

=SUMPRODUCT((INDIRECT("Management_PM!"&ADDRESS(3,22)&":"&ADDRESS(30000,22))="0-3")*--(INDIRECT("Management_PM!"&ADDRESS(3,24)&":"&ADDRESS(30000,24))))

Open in new window

Regards
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

barry houdiniCommented:
Why not use SUMIF? That function is more efficient and you can specify the whole column which shouldn't change depending on your import

=SUMIF('Management_ PM'!$V:$V,"0-3",'Management_PM'!$X:$X)

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Rgonzo1971 this now comes up with #Ref
0
JagwarmanAuthor Commented:
Barry this comes up with #VALUE! but if I include the numbers in the range it works. However, that is what my original problem is
0
barry houdiniCommented:
Try this version
=SUMIF('Management_ PM'!$V$3:INDEX('Management_PM'!$V$V,30000),"0-3",'Management_PM'!$X$3)
0
JagwarmanAuthor Commented:
Very odd they didn't work last week, today they work
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.