COUNTIFS or SUMPRODUCT with last row

Posted on 2014-11-29
Last Modified: 2014-12-01
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
Question by:DougDodge
  • 3
  • 3
LVL 24

Expert Comment

by:Phillip Burton
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.
LVL 24

Expert Comment

by:Phillip Burton
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.

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.....
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.


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.....
LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40472793
You've a ) too many.


=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")

Author Closing Comment

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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
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 viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now