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

 

[bw]

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

 

[Max]

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

 

[JE McGimpsey]

Take a look here:

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

 

[^'^BatAttaK^'^]

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

 

[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

 

[^'^BatAttaK^'^]

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

 

[Max]

You're welcome !
Thanks for the feedback ..

 

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

 

[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),1),--OFFSET($E$1,,,COUNTA(D),1))

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

 

[Biff]

Hmmm...

Just wondering if anybody read my follow-up post.

Biff

 

[Tom Ogilvy]

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

 

[Harlan Grove]

Hmmm...

Just wondering if anybody read my follow-up post.

Biff ....

....

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]

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

 

[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)
and then use 'rng' as the height argument for each of the ranges, will it
calc the formula each time, or just the once?

 

[Harlan Grove]

Ken Wright wrote...
....

Q - Gaps aside, if I define a named formula like 'rng' as =COUNTA(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.