ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Another Method or 2? (https://www.excelbanter.com/excel-discussion-misc-queries/129937-another-method-2-a.html)

JMay
Another Method or 2?
 
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..

T. Valko
Another Method or 2?
 
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,Look UpTable!B$7:B$12,0)-1,,,5))

Biff

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

T. Valko
Another Method or 2?
 
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

"T. Valko" wrote in message
...
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,Look UpTable!B$7:B$12,0)-1,,,5))

Biff

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

Roger Govier
Another Method or 2?
 
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..

JMay
Another Method or 2?
 
Thanks Biff;
Both are Cool,,
Jim

"T. Valko" wrote in message
:

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

"T. Valko" wrote in message
...
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,Look UpTable!B$7:B$12,0)-1,,,5))

Biff

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

JMay
Another Method or 2?
 
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..

Roger Govier
Another Method or 2?
 
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..

JMay
Another Method or 2?
 
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

"Roger Govier" wrote in message
:

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

T. Valko
Another Method or 2?
 
You're welcome. Thanks for the feedback!

Biff

"JMay" wrote in message
...
Thanks Biff;
Both are Cool,,
Jim

"T. Valko" wrote in message
:

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

"T. Valko" wrote in message
...
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,Look UpTable!B$7:B$12,0)-1,,,5))

Biff

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

Roger Govier
Another Method or 2?
 
Hi Jim

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

--
Regards

Roger Govier


"JMay" wrote in message
...
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

"Roger Govier" wrote in message
:

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


All times are GMT +1. The time now is 11:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com