Sumproduct frustrates me to no end.

H

HammerJoe

Hi,

I can't make sense of this and I need help see the error.

I have defined two dynamic names.
NewCall uses =OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AB:$AB),1)
ExistingCall uses =OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AC:$AC),1)

Both columns only contain numbers.

I also have another one with dates defined as TrackDate with formula
=OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA),1)

So same formula just different range.

Heres the funny part, if I use the formula =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate>=$B$94)*(TrackDate<>""),ExistingCall) it works
perfectly, but if I use instead =SUMPRODUCT((TrackDate<=$C
$94)*(TrackDate>=$B$94)*(TrackDate<>""),NewCall) it gives a #value
error.

The problem is NewCall, because if I delete it from the formula it
does return a result.
This is where I am stumped, I can't figure out whats wrong with
NewCall.
in the Define Name window, when the refers to box is selected it
highlits the range in the worksheet. The only difference between
newcall and Existingcall is that existingcall only has one empty row
at the bottom and NewCall has two rows.

How can I troubleshoot this?
 
H

HammerJoe

I just found out something.
NewCall works if using the formula =OFFSET(Data!$AB$3,0,0,COUNTA(Data!
$AC:$AC),1)

Does it means that theres something wrong with AB rows.
To check that the Offset formula is working, if I use CountA(newcall)
and counta(existingcall) they both give the same result, which is
correct.
I also checked the row format to make sure it is set up as general.
Any ideas?
 
T

T. Valko

You should base the height of all the ranges on a "key" column. It sounds
like your DATE column should be the key column.

So:

TrackDate
Refers to:
=OFFSET(Data!$AA$3,0,0,COUNTA(Data!$AA:$AA))

NewCall
Refers to:
=OFFSET(Data!$AB$3,0,0,COUNTA(Data!$AA:$AA))

ExistingCall
Refers to:
=OFFSET(Data!$AC$3,0,0,COUNTA(Data!$AA:$AA))

That way each range is based on the height of TrackDate
 
H

HammerJoe

Thanks for the help.

It makes sense, and I will do that.
But I still dont understand why AB does not work?
 
T

T. Valko

But I still dont understand why AB does not work?
All of your ranges might not be the same size (height).


--
Biff
Microsoft Excel MVP


Thanks for the help.

It makes sense, and I will do that.
But I still dont understand why AB does not work?
 
H

HammerJoe

Thats what I thought, but using Counta gives me the same result for
either AB and AC, in this case 20.
I dont understand this problem.
 
R

Roger Govier

Hi

What do you get if you try COUNT() on each of the ranges?
You say the ranges only contain numeric values, but you also talk about
ExistingCall having 1 empty row and Newcall having 2 empty rows.
If they are numbers, and they are truly empty, then you wouldn't get a
result of 20 in all cases, you would get 20, 19 and 18.

I suspect the problem lies with the content of those supposedly "empty"
cells.

As Biff says, in any case, it is always better to use what you would expect
to be the longest column of data as your count, and to use that for all
ranges to get a consistent length.
I always set a named range called lr (lastrow) as =COUNTA($AA:$AA) (as an
example)
then each range would be defined as
=$AA3:INDEX($AA:$AA,lr)
=$AB3:INDEX($AB:$AB,lr)
etc.

This method also avoids the use of the volatile Offset function.
 
H

HammerJoe

What do you get if you try COUNT() on each of the ranges?
You say the ranges only contain numeric values, but you also talk about
ExistingCall having 1 empty row and Newcall having 2 empty rows.
If they are numbers, and they are truly empty, then you wouldn't get a
result of 20 in all cases, you would get 20, 19 and 18.

I suspect the problem lies with the content of those supposedly "empty"
cells.
Click to expand...
That is my thinking too, but I did clear the cells and it always goes
below two rows from the last one whille AC only goes one down.
Is there a way to see whats in a cell?
I even used clear from the menu, same result.
As Biff says, in any case, it is always better to use what you would expect
to be the longest column of data as your count, and to use that for all
ranges to get a consistent length.
I always set a named range called lr (lastrow) as =COUNTA($AA:$AA) (as an
example)
then each range would be defined as
=$AA3:INDEX($AA:$AA,lr)
=$AB3:INDEX($AB:$AB,lr)
etc.

This method also avoids the use of the volatile Offset function.
Click to expand...

Thanks for the sugestion.
It is much more elgant and easy to use.
I will start using it right away.
 
T

T. Valko

=$AA3:INDEX($AA:$AA,lr)
=$AB3:INDEX($AB:$AB,lr)
This method also avoids the use of the volatile Offset function.
Click to expand...

I think I've read somewhere that defined name formulas recalc on every calc
so in essence they are volatile whether they use volatile functions or not.

In any case, the above named formulas are "volatile" at least once when the
particular file is opened. (I'm using Excel 2002 for this)

Try this:

Open a new wb
Put some random numbers in A1:A5
Insert>Name>Define
Name: rng
Refers to:
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNT(Sheet1!$A:$A))
OK

Enter this formula in C1: =SUM(rng)

Save the file then close it.
Open the file, don't do anything then close it.

You'll get the "save changes" message.
 
R

Roger Govier

Hi Biff

That might be so, on opening the workbook (or rather closing it), I don't
know why.
But try downloading Charles Williams test sheet for Volatile functions
http://www.decisionmodels.com/Downloads/VolatileFuncs.zip

Enter your range using Offset as a named range myRange, and use my method
using Index and call in myrRange2
On Charles' sheet, enter =SUM(myRange) and =SUM(myRange2) in any 2 cells on
the sheet, and copy the formula
=CalcSeqCountRef(Axx)
to refer to each of the cells holding those Calculations.
Press F9, and watch the results.
 
R

Roger Govier

OK. So this can explain why it is volatile when opening the book, and the
"dirty" flag is set, hence the prompt to Save when closing.

The Index() method is still far more efficient than using the truly volatile
Offset() function, which will cause recalculation on each and every change
within the workbook.
Besides that, the formula is shorter to type<vbg>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search two columns in list for first and last name and return Id 4
dropdown to show only part of data sheet 2
Reference Formula Help 3
Sumproduct;3 criteria 5
defined name - offset problem 5
Dynamic Name Range using VBA 1
Circular Reference - Why? 7
Sumproduct and CountA combined? Is that possible? 1

Top