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

Director, 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
Director, 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
Author 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
Author 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
Director, 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