COUNTIFS or SUMPRODUCT with last row

Trying to find a solution to using either a COUNTIFS or SUMPRODUCT on a worksheet that gets deleted and replaced daily. Because of the fact the worksheet gets deleted, a named Range goes into  the #REF zone... :(

Is there a way to use a COUNTIFS with a Last Row for part of the Range?

Currently the formula being used is =COUNTIFS('Orbit Data'!$M$2:$M$250000,$C4,'Orbit Data'!$W$2:$W$250000,"True") which is insane considering realistically the populated rows only go to 17,256, not 250,000

Also Would a SUMPRODUCT work faster? Or maybe INDEX/MATCH
DougDodgeAsked:
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I'll leave the insanity part, and concentrate on the it's two paragraphs :-)

Instead of using 'Orbit Data'!<<RangeOfCells>>, use Indirect("'Orbit Data'!<<RangeOfCells>>")

Note that the above started (with no spaces): indirect   (  "   '    Orbit

That should save the problem with the #Ref, assuming it is the spreadsheet Orbit Data that gets deleted and replaced daily.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Going for the insanity part, you can replace $250000 with

" & counta('Orbit Data',$m:m) & "

assuming that column M always has something in it - if not, choose another column which does.
0
DougDodgeAuthor Commented:
The INDIRECT works, I am not sure what it does to speed things up if at all.

The COUNTA gives an error.

=COUNTIFS(INDIRECT("'Orbit Data'!$M$2:$M" & COUNTA('Orbit Data'!$M:M) & ")"),$C5,INDIRECT("'Orbit Data'!$S$2:$S" & COUNTA('Orbit Data'!$M:M) & ")"),F$3,INDIRECT("'Orbit Data'!$W$2:$W" & COUNTA('Orbit Data'!$M:M) & ")"),"True")

Maybe I am missing something here.....
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

DougDodgeAuthor Commented:
This version :

=COUNTIFS(INDIRECT("'Orbit Data'!$M$2:$M$COUNTA('Orbit Data'!M:M)"),$C5,INDIRECT("'Orbit Data'!$S$2:$S$COUNTA('Orbit Data'!S:S)"),F$3,INDIRECT("'Orbit Data'!$W$2:$W$COUNTA('Orbit Data'!W:W)"),"True")

Gives an error of #REF!

No blank cells in columns M, S, or W
Exact same number of rows if I just use:
=COUNTA('Orbit Data'!M:M)
=COUNTA('Orbit Data'!S:S)
=COUNTA('Orbit Data'!W:W)

Not sure what is the problem.....
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You've a ) too many.

Try


=COUNTIFS(INDIRECT("'Orbit Data'!$M$2:$M" & COUNTA('Orbit Data'!$M:M)),$C5,INDIRECT("'Orbit Data'!$S$2:$S" & COUNTA('Orbit Data'!$M:M)),F$3,INDIRECT("'Orbit Data'!$W$2:$W" & COUNTA('Orbit Data'!$M:M)),"True")
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
DougDodgeAuthor Commented:
Works perfectly, thanks.....
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
Office Productivity

From novice to tech pro — start learning today.