I am stumped!

[^'^BatAttaK^'^]

I really hope someone can give me a hand with this one. I cannot
figure this one out for the life of me. I have Googled this to no
end.

If you take a look at the corresponding xls with this post I am
essentially trying to do a two dimensional lookup in the two rows of
data.

For example, I want to count the number of times "Name2" corresponds
to, let's say, "Peaches" "Cherries" or "Pumpkins".

Would this be really easy as a pivot? Yes...but I want the table to
be dynamic so that every time I dump data into a data tab the table
automatically updates.

www.batattak-records.com/test.xls


Ideas?

[Biff]

Hi!

In C7 enter this formula:

=SUMPRODUCT(--($H$5:$H$25=$B7),--($I$5:$I$25=C$6))

Copy across to E7 then copy down to E16.

Biff

>-----Original Message-----
>I really hope someone can give me a hand with this one.
I cannot
>figure this one out for the life of me. I have Googled
this to no
>end.
>
>If you take a look at the corresponding xls with this
post I am
>essentially trying to do a two dimensional lookup in the
two rows of
>data.
>
>For example, I want to count the number of times "Name2"
corresponds
>to, let's say, "Peaches" "Cherries" or "Pumpkins".
>
>Would this be really easy as a pivot? Yes...but I want
the table to
>be dynamic so that every time I dump data into a data tab
the table
>automatically updates.
>
>www.batattak-records.com/test.xls
>
>
>Ideas?
>.
>

[bw]

Biff,
Pretty cool! But I don't understand the formula. What does "--" mean?
Bernie


"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> In C7 enter this formula:
>
> =SUMPRODUCT(--($H$5:$H$25=$B7),--($I$5:$I$25=C$6))
>
> Copy across to E7 then copy down to E16.
>
> Biff
>
> >-----Original Message-----
> >I really hope someone can give me a hand with this one.
> I cannot
> >figure this one out for the life of me. I have Googled
> this to no
> >end.
> >
> >If you take a look at the corresponding xls with this
> post I am
> >essentially trying to do a two dimensional lookup in the
> two rows of
> >data.
> >
> >For example, I want to count the number of times "Name2"
> corresponds
> >to, let's say, "Peaches" "Cherries" or "Pumpkins".
> >
> >Would this be really easy as a pivot? Yes...but I want
> the table to
> >be dynamic so that every time I dump data into a data tab
> the table
> >automatically updates.
> >
> >www.batattak-records.com/test.xls
> >
> >
> >Ideas?
> >.
> >

[Max]

"bw" <[email protected]> wrote ..
> .. But I don't understand the formula. What does "--" mean?

Try the 2* responses in this previous post:
http://tinyurl.com/64py9

*Bob Phillips' link to his page, and
Jason's example and explanation, with a nice touch on its evolution

See also JE McGimpsey's:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[JE McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/doubleneg.html

In article <[email protected]>,
"bw" <[email protected]> wrote:

> Pretty cool! But I don't understand the formula. What does "--" mean?

[^'^BatAttaK^'^]

On Fri, 11 Feb 2005 23:41:02 -0800, "Biff" <[email protected]>
wrote:

>Hi!
>
>In C7 enter this formula:
>
>=SUMPRODUCT(--($H$5:$H$25=$B7),--($I$5:$I$25=C$6))
>
>Copy across to E7 then copy down to E16.
>
>Biff

Very nice! So if my data is on another tab this should read:

=SUMPRODUCT(--('data tab'!$A$1:$A$21=$B7),--('data
tab'!$B$1:$B$21=D$6))

However from what I can tell this must point to the _exact_ cell
references that I want to tally. It returns an error if I want to
tally everything in column A - ('data tab'!$A:$A=$B7) . Even if I
simply expand the scope of the tally via cell references ('data
tab'!$A$1:$A$2100=$B7) it will return an error.

How would I keep this completely dynamic and not limited by the cell
references?

[bw]

Thanks Max and JE. I appreciate your explanations and links.
Bernie

"bw" <[email protected]> wrote in message
news:[email protected]...
> Biff,
> Pretty cool! But I don't understand the formula. What does "--" mean?
> Bernie
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
> > Hi!
> >
> > In C7 enter this formula:
> >
> > =SUMPRODUCT(--($H$5:$H$25=$B7),--($I$5:$I$25=C$6))
> >
> > Copy across to E7 then copy down to E16.
> >
> > Biff
> >
> > >-----Original Message-----
> > >I really hope someone can give me a hand with this one.
> > I cannot
> > >figure this one out for the life of me. I have Googled
> > this to no
> > >end.
> > >
> > >If you take a look at the corresponding xls with this
> > post I am
> > >essentially trying to do a two dimensional lookup in the
> > two rows of
> > >data.
> > >
> > >For example, I want to count the number of times "Name2"
> > corresponds
> > >to, let's say, "Peaches" "Cherries" or "Pumpkins".
> > >
> > >Would this be really easy as a pivot? Yes...but I want
> > the table to
> > >be dynamic so that every time I dump data into a data tab
> > the table
> > >automatically updates.
> > >
> > >www.batattak-records.com/test.xls
> > >
> > >
> > >Ideas?
> > >.
> > >
>
>

[Max]

SUMPRODUCT cannot accept entire col references, e.g. 'data tab'!$A:$A

But you could always try in C7:

=SUMPRODUCT(--('data tab'!$A$1:$A$65535=$B7),--('data
tab'!$B$1:$B$65535=C$6))

with C7 copied across and filled down, as before

This should return the same results as before,
albeit it could be slow to re-calc due to the large ranges involved

Note that the ranges have to be identical:

'data tab'!$A$1:$A$65535
'data tab'!$B$1:$B$65535

otherwise you'll get #VALUE! errors
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"^'^BatAttaK^'^" <[email protected]> wrote in message
news:[email protected]...
> On Fri, 11 Feb 2005 23:41:02 -0800, "Biff" <[email protected]>
> wrote:
>
> >Hi!
> >
> >In C7 enter this formula:
> >
> >=SUMPRODUCT(--($H$5:$H$25=$B7),--($I$5:$I$25=C$6))
> >
> >Copy across to E7 then copy down to E16.
> >
> >Biff
>
> Very nice! So if my data is on another tab this should read:
>
> =SUMPRODUCT(--('data tab'!$A$1:$A$21=$B7),--('data
> tab'!$B$1:$B$21=D$6))
>
> However from what I can tell this must point to the _exact_ cell
> references that I want to tally. It returns an error if I want to
> tally everything in column A - ('data tab'!$A:$A=$B7) . Even if I
> simply expand the scope of the tally via cell references ('data
> tab'!$A$1:$A$2100=$B7) it will return an error.
>
> How would I keep this completely dynamic and not limited by the cell
> references?

[^'^BatAttaK^'^]

On Sun, 13 Feb 2005 01:35:12 +0800, "Max" <[email protected]>
wrote:

>SUMPRODUCT cannot accept entire col references, e.g. 'data tab'!$A:$A
>
>But you could always try in C7:
>
>=SUMPRODUCT(--('data tab'!$A$1:$A$65535=$B7),--('data
>tab'!$B$1:$B$65535=C$6))
>
>with C7 copied across and filled down, as before
>
>This should return the same results as before,
>albeit it could be slow to re-calc due to the large ranges involved
>
>Note that the ranges have to be identical:
>
>'data tab'!$A$1:$A$65535
>'data tab'!$B$1:$B$65535
>
>otherwise you'll get #VALUE! errors

That's perfect. Thank you! I tried something similar to this before I
posted but the values were not identical.

Many thanks to everyone that replied too.

[Biff]

Hi!

OK, let's make this really robust!

>So if my data is on another tab this should read:

=SUMPRODUCT(--('data tab'!$A$1:$A$21=$B7),--('data tab'!
$B$1:$B$21=D$6))

Yes.

>How would I keep this completely dynamic and not limited
>by the cell references?

Create dynamic named ranges and use error trapping for
when you expand the list of names/types.

Based on your test file .....

Create a dynamic range for Cols H and I.

Goto Insert>Name>Define
Name: Names
Refers to: =OFFSET($H$5,0,0,COUNTA($H$5:$H$1000),1)
Click Add.

Name: Types
Refers to: =OFFSET($I$5,0,0,COUNTA($I$5:$I$1000),1)
Click OK.

Since Sumproduct will not take whole columns as arguments,
you need to determine a range size that will not be
exceeded. That's what COUNTA() does. Also, there can be no
blank rows within the range.

Now, the formula would look like this:

=SUMPRODUCT(--(NAMES=$B7),--(TYPES=C$6))

However, as you've experienced, if you add to the NAMES
range and before you add to the TYPES range, both ranges
are temporarily different sizes and will cause errors.
This would only last until you add the corresponding value
in the TYPES range but it would still look like crap!

So, to prevent that:

=IF(ISERROR(SUMPRODUCT(--(NAMES=$B7),--
(TYPES=C$6))),"",SUMPRODUCT(--(NAMES=$B7),--(TYPES=C$6)))

Now, if a NAME does not have a matching TYPE the formula
as written returns zero. Seeing all those zero's makes the
table harder to read and just doesn't look good!

You can suppress the zero display a couple of ways:

1. Build it into the formula.
2. Goto Tools>Options>View Tab>Zero values.

I prefer to build it into the formula. Also, as you expand
the NAMES listed in your summary table in col B, you want
to test and make sure there is actually a NAME there. So
now the formula looks like this:

=IF(OR(ISERROR(SUMPRODUCT(--(Names=$B7),--
(Types=C$6))),$B7=""),"",IF(SUMPRODUCT(--(Names=$B7),--
(Types=C$6))=0,"",SUMPRODUCT(--(Names=$B7),--(Types=C$6))))

Much longer, somewhat more complicated, but more robust.

Biff

>-----Original Message-----
>On Fri, 11 Feb 2005 23:41:02 -0800, "Biff"
<[email protected]>
>wrote:
>
>>Hi!
>>
>>In C7 enter this formula:
>>
>>=SUMPRODUCT(--($H$5:$H$25=$B7),--($I$5:$I$25=C$6))
>>
>>Copy across to E7 then copy down to E16.
>>
>>Biff
>
>Very nice! So if my data is on another tab this should
read:
>
>=SUMPRODUCT(--('data tab'!$A$1:$A$21=$B7),--('data
>tab'!$B$1:$B$21=D$6))
>
>However from what I can tell this must point to the
_exact_ cell
>references that I want to tally. It returns an error if
I want to
>tally everything in column A - ('data tab'!$A:$A=$B7) .
Even if I
>simply expand the scope of the tally via cell references
('data
>tab'!$A$1:$A$2100=$B7) it will return an error.
>
>How would I keep this completely dynamic and not limited
by the cell
>references?
>.
>

[Max]

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"^'^BatAttaK^'^" <[email protected]> wrote
....
> That's perfect. Thank you! I tried something similar
> to this before I posted but the values were not identical.
> Many thanks to everyone that replied too.

[Tom Ogilvy]

Do that if you really want to slow down calculation in your worksheet. The
more cells you include in the formula, the longer it will take to calculate.
So I wouldn't blindly use 65535 unless absolutely necessary. You a range
that will accomodate you needs.

--
Regards,
Tom Ogilvy


"^'^BatAttaK^'^" <[email protected]> wrote in message
news:[email protected]...
> On Sun, 13 Feb 2005 01:35:12 +0800, "Max" <[email protected]>
> wrote:
>
> >SUMPRODUCT cannot accept entire col references, e.g. 'data tab'!$A:$A
> >
> >But you could always try in C7:
> >
> >=SUMPRODUCT(--('data tab'!$A$1:$A$65535=$B7),--('data
> >tab'!$B$1:$B$65535=C$6))
> >
> >with C7 copied across and filled down, as before
> >
> >This should return the same results as before,
> >albeit it could be slow to re-calc due to the large ranges involved
> >
> >Note that the ranges have to be identical:
> >
> >'data tab'!$A$1:$A$65535
> >'data tab'!$B$1:$B$65535
> >
> >otherwise you'll get #VALUE! errors
>
> That's perfect. Thank you! I tried something similar to this before I
> posted but the values were not identical.
>
> Many thanks to everyone that replied too.

[Ken Wright]

And to that end perhaps using a dynamic range that will grow as you need it
to. OFFSET or INDIRECT can often be used to do this, though whatever
parameters you use to determine the height of the range in one argument
should also be used to define the height of any other arguments within the
same formula, eg:-

=SUMPRODUCT(--OFFSET($D$1,,,COUNTA(D:D),1),--OFFSET($E$1,,,COUNTA(D:D),1))

Note the first range starts at D1 whereas the second range starts at E1,
BUT, the same COUNTA(D:D) is used in both cases to determine the height of
the respective range - This ensures both ranges are identical in height.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Do that if you really want to slow down calculation in your worksheet.
The
> more cells you include in the formula, the longer it will take to
calculate.
> So I wouldn't blindly use 65535 unless absolutely necessary. You a range
> that will accomodate you needs.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "^'^BatAttaK^'^" <[email protected]> wrote in message
> news:[email protected]...
> > On Sun, 13 Feb 2005 01:35:12 +0800, "Max" <[email protected]>
> > wrote:
> >
> > >SUMPRODUCT cannot accept entire col references, e.g. 'data tab'!$A:$A
> > >
> > >But you could always try in C7:
> > >
> > >=SUMPRODUCT(--('data tab'!$A$1:$A$65535=$B7),--('data
> > >tab'!$B$1:$B$65535=C$6))
> > >
> > >with C7 copied across and filled down, as before
> > >
> > >This should return the same results as before,
> > >albeit it could be slow to re-calc due to the large ranges involved
> > >
> > >Note that the ranges have to be identical:
> > >
> > >'data tab'!$A$1:$A$65535
> > >'data tab'!$B$1:$B$65535
> > >
> > >otherwise you'll get #VALUE! errors
> >
> > That's perfect. Thank you! I tried something similar to this before I
> > posted but the values were not identical.
> >
> > Many thanks to everyone that replied too.
>
>

[Biff]

Hmmm...

Just wondering if anybody read my follow-up post.

Biff

>-----Original Message-----
>And to that end perhaps using a dynamic range that will
grow as you need it
>to. OFFSET or INDIRECT can often be used to do this,
though whatever
>parameters you use to determine the height of the range
in one argument
>should also be used to define the height of any other
arguments within the
>same formula, eg:-
>
>=SUMPRODUCT(--OFFSET($D$1,,,COUNTA(D:D),1),--OFFSET
($E$1,,,COUNTA(D:D),1))
>
>Note the first range starts at D1 whereas the second
range starts at E1,
>BUT, the same COUNTA(D:D) is used in both cases to
determine the height of
>the respective range - This ensures both ranges are
identical in height.
>
>--
>Regards
> Ken....................... Microsoft MVP -
Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
>----------------------------------------------------------
------------------
> It's easier to beg forgiveness than ask
permission :-)
>----------------------------------------------------------
------------------
>
>"Tom Ogilvy" <[email protected]> wrote in message
>news:%[email protected]...
>> Do that if you really want to slow down calculation in
your worksheet.
>The
>> more cells you include in the formula, the longer it
will take to
>calculate.
>> So I wouldn't blindly use 65535 unless absolutely
necessary. You a range
>> that will accomodate you needs.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "^'^BatAttaK^'^" <[email protected]> wrote in message
>> news:[email protected]...
>> > On Sun, 13 Feb 2005 01:35:12 +0800, "Max"
<[email protected]>
>> > wrote:
>> >
>> > >SUMPRODUCT cannot accept entire col references,
e.g. 'data tab'!$A:$A
>> > >
>> > >But you could always try in C7:
>> > >
>> > >=SUMPRODUCT(--('data tab'!$A$1:$A$65535=$B7),--('data
>> > >tab'!$B$1:$B$65535=C$6))
>> > >
>> > >with C7 copied across and filled down, as before
>> > >
>> > >This should return the same results as before,
>> > >albeit it could be slow to re-calc due to the large
ranges involved
>> > >
>> > >Note that the ranges have to be identical:
>> > >
>> > >'data tab'!$A$1:$A$65535
>> > >'data tab'!$B$1:$B$65535
>> > >
>> > >otherwise you'll get #VALUE! errors
>> >
>> > That's perfect. Thank you! I tried something similar
to this before I
>> > posted but the values were not identical.
>> >
>> > Many thanks to everyone that replied too.
>>
>>
>
>
>.
>

[Tom Ogilvy]

I did - so I didn't propose anything about dynamic formulas. <g>

--
Regards,
Tom Ogilvy

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hmmm...
>
> Just wondering if anybody read my follow-up post.
>
> Biff
>
> >-----Original Message-----
> >And to that end perhaps using a dynamic range that will
> grow as you need it
> >to. OFFSET or INDIRECT can often be used to do this,
> though whatever
> >parameters you use to determine the height of the range
> in one argument
> >should also be used to define the height of any other
> arguments within the
> >same formula, eg:-
> >
> >=SUMPRODUCT(--OFFSET($D$1,,,COUNTA(D:D),1),--OFFSET
> ($E$1,,,COUNTA(D:D),1))
> >
> >Note the first range starts at D1 whereas the second
> range starts at E1,
> >BUT, the same COUNTA(D:D) is used in both cases to
> determine the height of
> >the respective range - This ensures both ranges are
> identical in height.
> >
> >--
> >Regards
> > Ken....................... Microsoft MVP -
> Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
> >----------------------------------------------------------
> ------------------
> > It's easier to beg forgiveness than ask
> permission :-)
> >----------------------------------------------------------
> ------------------
> >
> >"Tom Ogilvy" <[email protected]> wrote in message
> >news:%[email protected]...
> >> Do that if you really want to slow down calculation in
> your worksheet.
> >The
> >> more cells you include in the formula, the longer it
> will take to
> >calculate.
> >> So I wouldn't blindly use 65535 unless absolutely
> necessary. You a range
> >> that will accomodate you needs.
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "^'^BatAttaK^'^" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > On Sun, 13 Feb 2005 01:35:12 +0800, "Max"
> <[email protected]>
> >> > wrote:
> >> >
> >> > >SUMPRODUCT cannot accept entire col references,
> e.g. 'data tab'!$A:$A
> >> > >
> >> > >But you could always try in C7:
> >> > >
> >> > >=SUMPRODUCT(--('data tab'!$A$1:$A$65535=$B7),--('data
> >> > >tab'!$B$1:$B$65535=C$6))
> >> > >
> >> > >with C7 copied across and filled down, as before
> >> > >
> >> > >This should return the same results as before,
> >> > >albeit it could be slow to re-calc due to the large
> ranges involved
> >> > >
> >> > >Note that the ranges have to be identical:
> >> > >
> >> > >'data tab'!$A$1:$A$65535
> >> > >'data tab'!$B$1:$B$65535
> >> > >
> >> > >otherwise you'll get #VALUE! errors
> >> >
> >> > That's perfect. Thank you! I tried something similar
> to this before I
> >> > posted but the values were not identical.
> >> >
> >> > Many thanks to everyone that replied too.
> >>
> >>
> >
> >
> >.
> >

[Harlan Grove]

"Biff" <[email protected]> wrote...
>Hmmm...
>
>Just wondering if anybody read my follow-up post.
>
>Biff
....
>>=SUMPRODUCT(--OFFSET($D$1,,,COUNTA(D:D),1),
>>--OFFSET($E$1,,,COUNTA(D:D),1))
....

Normal caveats about any gaps in the ranges. Safer to find the last nonempty
cell and use the range down to it. Also, wouldn't this iterate through col D
twice in the COUNTA calls? If you want to optimize recalc efficiency, you'd
put the COUNTA call into a separate cell and reference that cell in the
OFFSET call.

[Harlan Grove]

"Tom Ogilvy" <[email protected]> wrote...
>Do that if you really want to slow down calculation in your worksheet. The
>more cells you include in the formula, the longer it will take to
calculate.
>So I wouldn't blindly use 65535 unless absolutely necessary. You a range
>that will accomodate you needs.
....

Depends. A SUMPRODUCT call referencing a 65535 row range without any
volatile function calls *could* be more efficient in the overall workbook
than using dynamic range references that require calls to volatile
functions. Why? Because the first SUMPRODUCT would only recalc when its
referenced ranges change, but the second would recalc whenever anything
changed.

[Ken Wright]

And I'm still sat here going 'what follow up post?' :-)

Mind you I seem to be getting lots of replies show up in OE without the
original posts? If I hit get next xxx headers then some of them show up,
but this is bugging me :-(

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip>

[Ken Wright]

Granted on both counts, but unless the second really gave me any calc issues
I'd just as soon have it all in a single formula. If I was using multiple
ranges as criteria though, then not just for recalc issues, but also for
sheer formula size I'd go your way on that.

Q - Gaps aside, if I define a named formula like 'rng' as =COUNTA(D:D)
and then use 'rng' as the height argument for each of the ranges, will it
calc the formula each time, or just the once?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Biff" <[email protected]> wrote...
> >Hmmm...
> >
> >Just wondering if anybody read my follow-up post.
> >
> >Biff
> ...
> >>=SUMPRODUCT(--OFFSET($D$1,,,COUNTA(D:D),1),
> >>--OFFSET($E$1,,,COUNTA(D:D),1))
> ...
>
> Normal caveats about any gaps in the ranges. Safer to find the last
nonempty
> cell and use the range down to it. Also, wouldn't this iterate through col
D
> twice in the COUNTA calls? If you want to optimize recalc efficiency,
you'd
> put the COUNTA call into a separate cell and reference that cell in the
> OFFSET call.
>
>

[Harlan Grove]

Ken Wright wrote...
....
>Q - Gaps aside, if I define a named formula like 'rng' as
=COUNTA(D:D)
>and then use 'rng' as the height argument for each of the ranges,
will it
>calc the formula each time, or just the once?
....

Test and benchmark.

Testing. Using the UDF

Function foo() As Boolean
Static n As Long
MsgBox n
n = n + 1
End Function

I defined the name foobar referring to =foo(). Then I enter the cell
formula

=foobar+foobar

I get the MsgBox dialog appearing twice with sequential values. Do you?

Benchmarking. Create separate workbooks, one without the defined name
and the other with it and using it. Use a macro to recalc those
workbooks several thousand times and compare the elapsed times between
just before the first recalc and just after the last recalc.

I may be wrong in this case (don't bank on it), but my results support
the conclusion that defined names are merely aliases one may use to
shorten formulas. Excel evaluates formulas by treating references to
defined names as part of the formulas that must be evaluated as they
appear, so if a name appears several times, it'll be evaluated several
times.

[Ken Wright]

LOL - sorry Harlan, shouldn't have been so lazy.

As stated - Two dialog boxes with seq values :-)


A1:F10000 misc values of 1- 6
G1G10000 =rand()

looping 10,000 times in each case.

Scenario1
L1 = COUNTA(A:A)

J1
=SUMPRODUCT(--(OFFSET($A$1,,,L1,1)=1),--(OFFSET($B$1,,,L1,1)=2),--(OFFSET($C
$1,,,L1,1)=3),--(OFFSET($D$1,,,L1,1)=4),--(OFFSET($E$1,,,L1,1)=5),--(OFFSET(
$F$1,,,L1,1)=6),OFFSET($F$1,,,L1,1))

Scenario2
J1
=SUMPRODUCT(--(OFFSET($A$1,,,COUNTA(A:A),1)=1),--(OFFSET($B$1,,,COUNTA(A:A),
1)=2),--(OFFSET($C$1,,,COUNTA(A:A),1)=3),--(OFFSET($D$1,,,COUNTA(A:A),1)=4),
--(OFFSET($E$1,,,COUNTA(A:A),1)=5),--(OFFSET($F$1,,,COUNTA(A:A),1)=6),OFFSET
($F$1,,,COUNTA(A:A),1))

Scenario3
rng defined as =COUNTA($A:$A)

J1
=SUMPRODUCT(--(OFFSET($A$1,,,rng,1)=1),--(OFFSET($B$1,,,rng,1)=2),--(OFFSET(
$C$1,,,rng,1)=3),--(OFFSET($D$1,,,rng,1)=4),--(OFFSET($E$1,,,rng,1)=5),--(OF
FSET($F$1,,,rng,1)=6),OFFSET($F$1,,,rng,1))

Each run twice to ensure consistent values - times to run:-

S1 = 04:19
S2 = 04:40
S3 = 04:40

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Ken Wright wrote...
> ...
> >Q - Gaps aside, if I define a named formula like 'rng' as
> =COUNTA(D:D)
> >and then use 'rng' as the height argument for each of the ranges,
> will it
> >calc the formula each time, or just the once?
> ...
>
> Test and benchmark.
>
> Testing. Using the UDF
>
> Function foo() As Boolean
> Static n As Long
> MsgBox n
> n = n + 1
> End Function
>
> I defined the name foobar referring to =foo(). Then I enter the cell
> formula
>
> =foobar+foobar
>
> I get the MsgBox dialog appearing twice with sequential values. Do you?
>
> Benchmarking. Create separate workbooks, one without the defined name
> and the other with it and using it. Use a macro to recalc those
> workbooks several thousand times and compare the elapsed times between
> just before the first recalc and just after the last recalc.
>
> I may be wrong in this case (don't bank on it), but my results support
> the conclusion that defined names are merely aliases one may use to
> shorten formulas. Excel evaluates formulas by treating references to
> defined names as part of the formulas that must be evaluated as they
> appear, so if a name appears several times, it'll be evaluated several
> times.
>

[Ken Wright]

> looping 10,000 times in each case.

and calcing each time obviously :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip>

[Max]

Noticed that you have requested for your post/all threads?? *not* to be
archived in Google (Re: the header in your original post in Google - message
will be removed on Feb 18, 10:49 pm??). Just thought it'll be quite a loss
for the community's future reference to have this event occur in view of the
many posts / insights by the many who have since added-on so much value to
the subject/discussions. Any chance that you could reconsider your
request?? Gotta act quickly, time's running out ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Harlan Grove]

Max wrote...
>Noticed that you have requested for your post/all threads?? *not* to
be
>archived in Google (Re: the header in your original post in Google -
message
>will be removed on Feb 18, 10:49 pm??). Just thought it'll be quite a
loss
>for the community's future reference to have this event occur in view
of the
>many posts / insights by the many who have since added-on so much
value to
>the subject/discussions. Any chance that you could reconsider your
>request?? Gotta act quickly, time's running out ..

Do you understand how the Google Groups archive works? Do you
understand how optional tags in newsgroup messages work? Just because
the OP's message goes bye-bye in 3 days doesn't mean all the responses
do too.

The OP's message includes an X-No-Archive tag set to yes. To the extent
I've checked, none of the responses aside from the OP's follow-ups
include this tag. Therefore, the responses will remain even if the OP's
original message and follow-ups are purged.

Now, since so few (so darned few) respondents in these newsgroups
either know how to snip quoted material from previous posts or that
they should snip all but the most relevant preceding bits, there are
several FULL copies of the OP's original message and follow-ups in the
other responses.

HOW MANY COPIES OF THE OP'S ORIGINAL MESSAGE AND FOLLOW-UPS DO YOU
NEED?!

[And how would you expect the OP could alter the X-No-Archive tag in an
already posted newsgroup article?]

[Max]

Thanks for the in-depth explanations, Harlan !
It met the call ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Sandy Mann]

Harlan Grove" <[email protected]> wrote in message
news:[email protected]...> Now, since so
few (so darned few) respondents in these newsgroups
> either know how to snip quoted material from previous posts or that
> they should snip all but the most relevant preceding bits, there are
> several FULL copies of the OP's original message and follow-ups in the
> other responses.
>
>

At last! Now I feel like I have been of some use around here! <g>

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"