Solved

# COUNTIFS or SUMPRODUCT with last row

Posted on 2014-11-29

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