Another Method or 2?

J

JMay

In A1:C2 I have:

CC Method1 Method2
01016000 4,263.00 4,263.00

Cell B2 contains:

=SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(LookUpTable!$C$7:$G$12))

Cell C2 contains:

{=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTable!$C$7:$G$12)}

Both Methods work fine; I'd just like to have another 1 or 2 methods
of obtaining the same answer. Any suggestions?

Here is my $B$7:$B$12 on LookupTable:

01016000 3,245.00 43.00 432.00 543.00
01016100 456.00 567.00 543.00
01016200 654.00 654.00
01016300 654.00 543.00 567.00
01016400 3,458.00 456.00 7,323.00
01016500 6,788.00 567.00

Thanks in Advance..
 
T

T. Valko

Here's one but it's not any better than what you have already. In fact, I'd
never use it! But here it is:

=SUM(OFFSET(LookUpTable!C$7,MATCH(Summary!A$2,LookUpTable!B$7:B$12,0)-1,,,5))

Biff
 
T

T. Valko

Here's another one. I might even use this one over the Sumproduct version:

Defined names:

Table
Refers to: LookUpTable!$C$7:$G$12

ID
Refers to: LookUpTable!$B$7:$B$12

=SUM(INDEX(Table,MATCH(Summary!A2,ID,0),))

Biff
 
R

Roger Govier

Hi Jim

If you used another helper column on your lookup table, say H7:H12 with
the formula
=SUM(C7:G12)
then the simple
=SUMIF(LookUpTable!$B$7:$B$12,Summary!A2,LookUpTable!$H$7:$H$12)

On the range sizes used, speed difference would not be noticed, but on
larger arrays, it would certainly be faster than the array formula, and
probably faster than the Sumproduct solution.
 
J

JMay

Thanks Biff;
Both are Cool,,
Jim

Here's another one. I might even use this one over the Sumproduct version:

Defined names:

Table
Refers to: LookUpTable!$C$7:$G$12

ID
Refers to: LookUpTable!$B$7:$B$12

=SUM(INDEX(Table,MATCH(Summary!A2,ID,0),))

Biff
Click to expand...
 
J

JMay

Thanks Roger
I was trying to avoid the helper-column "thing";
Good to keep in mind the speed factor variances, however.
Thanks,
Jim
 
R

Roger Govier

Hi Jim
I was trying to avoid the helper-column "thing"
Click to expand...
I hear what you say, and I respect your opinion.
I do find it strange though, how averse people are to using extra or
"helper" columns.

Whilst I often post answers to people using perhaps long or complicated
formulae, when I am building applications for clients (or myself), I
often use helper columns to keep the formulae simpler and more easily
maintainable, as well as faster when dealing with large datasets. These
extra columns are invariably hidden. Seldom (if ever) has the number of
available columns been a problem even with 256, and now with 1024 in
XL2007 it never will be.

I practically always start with rows 1:10 and columns A:E hidden on all
sheets, so F11 is my "A1".
Then I always know I have spare columns and rows available for other
tasks, even without inserting and hiding them within the body of data.
 
J

JMay

Roger, Thanks -- this is an added help
I use helper columns VERY Frequently also,
But in this case the Table info sheet is
an Essbase (Hyperion) type sheet which when it is "refreshed"
It loses any pre-existing formulas, grrrrrr

Much obliged for your comments (I will keep
them in mind)

Jim May
 
R

Roger Govier

Hi Jim

Sounds like a case for VB code to add back all the formulae, even
including insertion of helper columns where necessary<bg>.
 
Top