Solved

COUNTIFS or SUMPRODUCT with last row

Posted on 2014-11-29
172 Views
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
0
Question by:DougDodge
• 3
• 3

LVL 24

Expert Comment

ID: 40472281
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

LVL 24

Expert Comment

ID: 40472284
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 Comment

ID: 40472502
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 Comment

ID: 40472537
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

LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40472793
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

Author Closing Comment

ID: 40473762
Works perfectly, thanks.....
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

To stay competitive, modern businesses must adapt and stay innovative, and this is increasingly only possible by working with outside talent. Managers and executives have understood the power of outsourcing for quite some time, but traditional clien…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…