I am stumped!

B

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
 
M

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
 
B

Biff

Hi!

OK, let's make this really robust!
So if my data is on another tab this should read:
Click to expand...

=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?
Click to expand...

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
 
T

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.
 
K

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.
 
H

Harlan Grove

Biff said:
Hmmm...

Just wondering if anybody read my follow-up post.

Biff ....
Click to expand...
....

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.
 
H

Harlan Grove

Tom Ogilvy said:
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.
Click to expand...
....

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.
 
K

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 :-(
 
K

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?
 
H

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?
Click to expand...
....

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.
 
K

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
 
M

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 ..
 
H

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 ..
Click to expand...

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?]
 

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

Tricky Formulas 14
Goal Seeking via Excel 0
Simple Calculation Bug 1
If Multiple Criteria 1
Payment calculation 1
SUMPRODUCT formula is summing text and I want it to stop doing that 2
Formula for tracking Nassau golf bets 8
Excel 2013: AutoFilter and SUM() 1

Top