Cross refrence an index table and sum in one step?

[Robbro]

Currently I get financial info broken down into accounts, the number accounts
may change every month. I have an index spreadsheet that lists every
possible account and what category it goes into.
Currently I do a Vlookup from the financial spreadsheet to my index to get
the category name on the financial spread sheet, then sumif at the bottom to
summarize, not a big deal really, but I have multiple spreadsheets I do this
on every month so I was wondering if there was any way possible to do this in
one step. Something like a sumif(vlookup(a1:a100,indextable
a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account name
range and b1:b100 is my dollar amounts
I know the above doesnt work becacuse vlookup cant use a range, but its the
general gist of what I would like to do. I've done some neat things with
sumproduct, but since my index range is not the same size as my account
range, I know it wont work.
Is there some other neat trick to get this to work in one single equation?

 

[T. Valko]

Maybe something like this...

This is your lookup table in the range F1:G6

A...56
B...3
C...95
D...84
E...60
F...46

These are your account names in the range A1:A5 -

C
A
E
D
A

=SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6))

 

[Robbro]

I think I described my situation poorly. My lookup table is as follows

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare
.....

My Financials are as follows

A 100
C 200
D 75
E 90

I then insert into the financials a vlookup to put Empl. Wellfare next to A
and E, Var Ohead next to C and Fixed Ohead next to D

Then below that I lay my categories out
Empl. Welfare
Var Ohead
Fixed Ohead

and use sumif to add up all of each category, this works ok, but I was
looking to eliminate a step and just have one function in the bottom do the
lookup to see what category each account goes into and sum them up in one
step.
I've tried an array but when I try to use vlookup="Empl. Welfare" it
apparently returns true for EVERYTHING if just one item in my range is Empl.
Welfare.

{=SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}

The above sums everything in the range B10:B100, if this worked I would have
my solution, I feel I'm close to it, but not sure its possible to ever get it
to work?

 

[T. Valko]

Sorry, but I'm still not understanding this.

Based on your posted sample data:

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare

A 100
C 200
D 75
E 90

Are you "looking up" A,B,C,D,E from A,C,D,E ?

--
Biff
Microsoft Excel MVP

I think I described my situation poorly. My lookup table is as follows

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare
....

My Financials are as follows

A 100
C 200
D 75
E 90

I then insert into the financials a vlookup to put Empl. Wellfare next to
A
and E, Var Ohead next to C and Fixed Ohead next to D

Then below that I lay my categories out
Empl. Welfare
Var Ohead
Fixed Ohead

and use sumif to add up all of each category, this works ok, but I was
looking to eliminate a step and just have one function in the bottom do
the
lookup to see what category each account goes into and sum them up in one
step.
I've tried an array but when I try to use vlookup="Empl. Welfare" it
apparently returns true for EVERYTHING if just one item in my range is
Empl.
Welfare.

{=SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}

The above sums everything in the range B10:B100, if this worked I would
have
my solution, I feel I'm close to it, but not sure its possible to ever get
it
to work?

Maybe something like this...

This is your lookup table in the range F1:G6

A...56
B...3
C...95
D...84
E...60
F...46

These are your account names in the range A1:A5 -

C
A
E
D
A

=SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6))

--
Biff
Microsoft Excel MVP





.

 

[Robbro]

I'm looking up account A to see that it goes into my category of Empl
Welfare. Multiple accounts will go into each category. I have generally
around 100 accounts (varies from month to month) and about 15 catgories, each
account goes into only 1 category, thats what my vlookup to my index table
tells me.
Then at bottom I summarize by listing each category and using sumif based on
the category assigned to the account to add up all accounts that go into that
specific category. I was just looking for a way to eliminate inserting
vlookups into the financial data and have everything taken care of in 1 step

In other words an equation I can put to the right of the following
categories that will look up each and sum each account that goes into that
category per my index table
If vlookup worked in an array, this is what I think would work.... however
it only evaluates vlookup once apparently, not once for everything in the
range of A10:A100
={SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}
The above sums EVERYTHING in B10:B100 instead of just those for which my
vlookup=a112, just wondering if there was a different way to skin this cat.

Been looking to try some match or sumproduct.... but nothing ever seems to
work.

Sorry, but I'm still not understanding this.

Based on your posted sample data:

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare

A 100
C 200
D 75
E 90

Are you "looking up" A,B,C,D,E from A,C,D,E ?

--
Biff
Microsoft Excel MVP

I think I described my situation poorly. My lookup table is as follows

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare
....

My Financials are as follows

A 100
C 200
D 75
E 90

I then insert into the financials a vlookup to put Empl. Wellfare next to
A
and E, Var Ohead next to C and Fixed Ohead next to D

Then below that I lay my categories out
Empl. Welfare
Var Ohead
Fixed Ohead

and use sumif to add up all of each category, this works ok, but I was
looking to eliminate a step and just have one function in the bottom do
the
lookup to see what category each account goes into and sum them up in one
step.
I've tried an array but when I try to use vlookup="Empl. Welfare" it
apparently returns true for EVERYTHING if just one item in my range is
Empl.
Welfare.

{=SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}

The above sums everything in the range B10:B100, if this worked I would
have
my solution, I feel I'm close to it, but not sure its possible to ever get
it
to work?

Maybe something like this...

This is your lookup table in the range F1:G6

A...56
B...3
C...95
D...84
E...60
F...46

These are your account names in the range A1:A5 -

C
A
E
D
A

=SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6))

--
Biff
Microsoft Excel MVP


Currently I get financial info broken down into accounts, the number
accounts
may change every month. I have an index spreadsheet that lists every
possible account and what category it goes into.
Currently I do a Vlookup from the financial spreadsheet to my index to
get
the category name on the financial spread sheet, then sumif at the
bottom
to
summarize, not a big deal really, but I have multiple spreadsheets I do
this
on every month so I was wondering if there was any way possible to do
this
in
one step. Something like a sumif(vlookup(a1:a100,indextable
a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account
name
range and b1:b100 is my dollar amounts
I know the above doesnt work becacuse vlookup cant use a range, but its
the
general gist of what I would like to do. I've done some neat things
with
sumproduct, but since my index range is not the same size as my account
range, I know it wont work.
Is there some other neat trick to get this to work in one single
equation?


.

.

 

[T. Valko]

I'm sure this can be done but I'm having a hard time visualizing your setup.
If I could see the file I'm sure we can get this to work.

If you want to put together a *small* sample file and send it to me I'll see
what I can do. Just make sure you include the results you expect. If you
want to do that you can contact me at:

xl can help at comcast period net

Remove "can" and change the obvious.

I have both Excel 2002 and 2007 so either of those file formats will be
fine.

--
Biff
Microsoft Excel MVP

I'm looking up account A to see that it goes into my category of Empl
Welfare. Multiple accounts will go into each category. I have generally
around 100 accounts (varies from month to month) and about 15 catgories,
each
account goes into only 1 category, thats what my vlookup to my index table
tells me.
Then at bottom I summarize by listing each category and using sumif based
on
the category assigned to the account to add up all accounts that go into
that
specific category. I was just looking for a way to eliminate inserting
vlookups into the financial data and have everything taken care of in 1
step

In other words an equation I can put to the right of the following
categories that will look up each and sum each account that goes into that
category per my index table
If vlookup worked in an array, this is what I think would work.... however
it only evaluates vlookup once apparently, not once for everything in the
range of A10:A100
={SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}
The above sums EVERYTHING in B10:B100 instead of just those for which my
vlookup=a112, just wondering if there was a different way to skin this
cat.

Been looking to try some match or sumproduct.... but nothing ever seems to
work.

Sorry, but I'm still not understanding this.

Based on your posted sample data:

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare

A 100
C 200
D 75
E 90

Are you "looking up" A,B,C,D,E from A,C,D,E ?

--
Biff
Microsoft Excel MVP

I think I described my situation poorly. My lookup table is as follows

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare
....

My Financials are as follows

A 100
C 200
D 75
E 90

I then insert into the financials a vlookup to put Empl. Wellfare next
to
A
and E, Var Ohead next to C and Fixed Ohead next to D

Then below that I lay my categories out
Empl. Welfare
Var Ohead
Fixed Ohead

and use sumif to add up all of each category, this works ok, but I was
looking to eliminate a step and just have one function in the bottom do
the
lookup to see what category each account goes into and sum them up in
one
step.
I've tried an array but when I try to use vlookup="Empl. Welfare" it
apparently returns true for EVERYTHING if just one item in my range is
Empl.
Welfare.

{=SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}

The above sums everything in the range B10:B100, if this worked I would
have
my solution, I feel I'm close to it, but not sure its possible to ever
get
it
to work?

:

Maybe something like this...

This is your lookup table in the range F1:G6

A...56
B...3
C...95
D...84
E...60
F...46

These are your account names in the range A1:A5 -

C
A
E
D
A

=SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6))

--
Biff
Microsoft Excel MVP


Currently I get financial info broken down into accounts, the number
accounts
may change every month. I have an index spreadsheet that lists
every
possible account and what category it goes into.
Currently I do a Vlookup from the financial spreadsheet to my index
to
get
the category name on the financial spread sheet, then sumif at the
bottom
to
summarize, not a big deal really, but I have multiple spreadsheets I
do
this
on every month so I was wondering if there was any way possible to
do
this
in
one step. Something like a sumif(vlookup(a1:a100,indextable
a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account
name
range and b1:b100 is my dollar amounts
I know the above doesnt work becacuse vlookup cant use a range, but
its
the
general gist of what I would like to do. I've done some neat things
with
sumproduct, but since my index range is not the same size as my
account
range, I know it wont work.
Is there some other neat trick to get this to work in one single
equation?


.

.

 

[Robbro]

Thanks for the offer, but I've gotten a solution, though not what I
originally wanted, its perfectly functional. I copied my index into each
file and look up from the account in that to the financials to get the $
amounts I need then summarize, I was basically trying to do the opposite
originally. This takes a lot of space up on the sheet, but its only a
summary sheet so thats ok.

I'm sure this can be done but I'm having a hard time visualizing your setup.
If I could see the file I'm sure we can get this to work.

If you want to put together a *small* sample file and send it to me I'll see
what I can do. Just make sure you include the results you expect. If you
want to do that you can contact me at:

xl can help at comcast period net

Remove "can" and change the obvious.

I have both Excel 2002 and 2007 so either of those file formats will be
fine.

--
Biff
Microsoft Excel MVP

I'm looking up account A to see that it goes into my category of Empl
Welfare. Multiple accounts will go into each category. I have generally
around 100 accounts (varies from month to month) and about 15 catgories,
each
account goes into only 1 category, thats what my vlookup to my index table
tells me.
Then at bottom I summarize by listing each category and using sumif based
on
the category assigned to the account to add up all accounts that go into
that
specific category. I was just looking for a way to eliminate inserting
vlookups into the financial data and have everything taken care of in 1
step

In other words an equation I can put to the right of the following
categories that will look up each and sum each account that goes into that
category per my index table
If vlookup worked in an array, this is what I think would work.... however
it only evaluates vlookup once apparently, not once for everything in the
range of A10:A100
={SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}
The above sums EVERYTHING in B10:B100 instead of just those for which my
vlookup=a112, just wondering if there was a different way to skin this
cat.

Been looking to try some match or sumproduct.... but nothing ever seems to
work.

Sorry, but I'm still not understanding this.

Based on your posted sample data:

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare

A 100
C 200
D 75
E 90

Are you "looking up" A,B,C,D,E from A,C,D,E ?

--
Biff
Microsoft Excel MVP


I think I described my situation poorly. My lookup table is as follows

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare
....

My Financials are as follows

A 100
C 200
D 75
E 90

I then insert into the financials a vlookup to put Empl. Wellfare next
to
A
and E, Var Ohead next to C and Fixed Ohead next to D

Then below that I lay my categories out
Empl. Welfare
Var Ohead
Fixed Ohead

and use sumif to add up all of each category, this works ok, but I was
looking to eliminate a step and just have one function in the bottom do
the
lookup to see what category each account goes into and sum them up in
one
step.
I've tried an array but when I try to use vlookup="Empl. Welfare" it
apparently returns true for EVERYTHING if just one item in my range is
Empl.
Welfare.

{=SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}

The above sums everything in the range B10:B100, if this worked I would
have
my solution, I feel I'm close to it, but not sure its possible to ever
get
it
to work?

:

Maybe something like this...

This is your lookup table in the range F1:G6

A...56
B...3
C...95
D...84
E...60
F...46

These are your account names in the range A1:A5 -

C
A
E
D
A

=SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6))

--
Biff
Microsoft Excel MVP


Currently I get financial info broken down into accounts, the number
accounts
may change every month. I have an index spreadsheet that lists
every
possible account and what category it goes into.
Currently I do a Vlookup from the financial spreadsheet to my index
to
get
the category name on the financial spread sheet, then sumif at the
bottom
to
summarize, not a big deal really, but I have multiple spreadsheets I
do
this
on every month so I was wondering if there was any way possible to
do
this
in
one step. Something like a sumif(vlookup(a1:a100,indextable
a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my account
name
range and b1:b100 is my dollar amounts
I know the above doesnt work becacuse vlookup cant use a range, but
its
the
general gist of what I would like to do. I've done some neat things
with
sumproduct, but since my index range is not the same size as my
account
range, I know it wont work.
Is there some other neat trick to get this to work in one single
equation?


.



.

.

 

[T. Valko]

OK, good luck!

--
Biff
Microsoft Excel MVP

Thanks for the offer, but I've gotten a solution, though not what I
originally wanted, its perfectly functional. I copied my index into each
file and look up from the account in that to the financials to get the $
amounts I need then summarize, I was basically trying to do the opposite
originally. This takes a lot of space up on the sheet, but its only a
summary sheet so thats ok.

I'm sure this can be done but I'm having a hard time visualizing your
setup.
If I could see the file I'm sure we can get this to work.

If you want to put together a *small* sample file and send it to me I'll
see
what I can do. Just make sure you include the results you expect. If you
want to do that you can contact me at:

xl can help at comcast period net

Remove "can" and change the obvious.

I have both Excel 2002 and 2007 so either of those file formats will be
fine.

--
Biff
Microsoft Excel MVP

I'm looking up account A to see that it goes into my category of Empl
Welfare. Multiple accounts will go into each category. I have
generally
around 100 accounts (varies from month to month) and about 15
catgories,
each
account goes into only 1 category, thats what my vlookup to my index
table
tells me.
Then at bottom I summarize by listing each category and using sumif
based
on
the category assigned to the account to add up all accounts that go
into
that
specific category. I was just looking for a way to eliminate inserting
vlookups into the financial data and have everything taken care of in 1
step

In other words an equation I can put to the right of the following
categories that will look up each and sum each account that goes into
that
category per my index table
If vlookup worked in an array, this is what I think would work....
however
it only evaluates vlookup once apparently, not once for everything in
the
range of A10:A100
={SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}
The above sums EVERYTHING in B10:B100 instead of just those for which
my
vlookup=a112, just wondering if there was a different way to skin this
cat.

Been looking to try some match or sumproduct.... but nothing ever seems
to
work.
:

Sorry, but I'm still not understanding this.

Based on your posted sample data:

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare

A 100
C 200
D 75
E 90

Are you "looking up" A,B,C,D,E from A,C,D,E ?

--
Biff
Microsoft Excel MVP


I think I described my situation poorly. My lookup table is as
follows

A Empl. Welfare
B Empl. Welfare
C Var Ohead
D Fixed Ohead
E Empl. Welfare
....

My Financials are as follows

A 100
C 200
D 75
E 90

I then insert into the financials a vlookup to put Empl. Wellfare
next
to
A
and E, Var Ohead next to C and Fixed Ohead next to D

Then below that I lay my categories out
Empl. Welfare
Var Ohead
Fixed Ohead

and use sumif to add up all of each category, this works ok, but I
was
looking to eliminate a step and just have one function in the bottom
do
the
lookup to see what category each account goes into and sum them up
in
one
step.
I've tried an array but when I try to use vlookup="Empl. Welfare" it
apparently returns true for EVERYTHING if just one item in my range
is
Empl.
Welfare.

{=SUM(IF(VLOOKUP(A10:A100,'[Income Statement
crossreference.xlsb]Sheet1'!$A$10:$B$107,2,FALSE)=A112,1,0)*B10:B100)}

The above sums everything in the range B10:B100, if this worked I
would
have
my solution, I feel I'm close to it, but not sure its possible to
ever
get
it
to work?

:

Maybe something like this...

This is your lookup table in the range F1:G6

A...56
B...3
C...95
D...84
E...60
F...46

These are your account names in the range A1:A5 -

C
A
E
D
A

=SUMPRODUCT(SUMIF(F1:F6,A1:A5,G1:G6))

--
Biff
Microsoft Excel MVP


Currently I get financial info broken down into accounts, the
number
accounts
may change every month. I have an index spreadsheet that lists
every
possible account and what category it goes into.
Currently I do a Vlookup from the financial spreadsheet to my
index
to
get
the category name on the financial spread sheet, then sumif at
the
bottom
to
summarize, not a big deal really, but I have multiple
spreadsheets I
do
this
on every month so I was wondering if there was any way possible
to
do
this
in
one step. Something like a sumif(vlookup(a1:a100,indextable
a1:b1000,2,false),categoryname,b1:b100) where a1:a100 is my
account
name
range and b1:b100 is my dollar amounts
I know the above doesnt work becacuse vlookup cant use a range,
but
its
the
general gist of what I would like to do. I've done some neat
things
with
sumproduct, but since my index range is not the same size as my
account
range, I know it wont work.
Is there some other neat trick to get this to work in one single
equation?


.



.

.