[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

COUNTIFS or SUMPRODUCT with last row

Posted on 2014-11-29
6
Medium Priority
?
209 Views
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
0
Comment
Question by:DougDodge
  • 3
  • 3
6 Comments
 
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.
0
 
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.
0
 

Author Comment

by:DougDodge
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 

Author Comment

by:DougDodge
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

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

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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
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.

591 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