Hi Jim

I was trying to avoid the helper-column "thing"
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.


--
Regards

Roger Govier


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


"Roger Govier" wrote in message
:

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,LookUpTab le!$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.

--
Regards

Roger Govier


"JMay" wrote in message
...
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)*(L ookUpTable!$C$7:$G$12))

Cell C2 contains:

{=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$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..