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