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?

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" <(E-Mail Removed)> wrote in message
news:E769288B-779B-488F-870D-(E-Mail Removed)...
> 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?

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:E769288B-779B-488F-870D-(E-Mail Removed)...
> > 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?
>
>
> .
>

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


"Robbro" <(E-Mail Removed)> wrote in message
news:B1A70A43-35D7-416F-BBDB-(E-Mail Removed)...
>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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:E769288B-779B-488F-870D-(E-Mail Removed)...
>> > 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?
>>
>>
>> .
>>

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.
"T. Valko" wrote:

> 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
>
>
> "Robbro" <(E-Mail Removed)> wrote in message
> news:B1A70A43-35D7-416F-BBDB-(E-Mail Removed)...
> >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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:E769288B-779B-488F-870D-(E-Mail Removed)...
> >> > 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?
> >>
> >>
> >> .
> >>
>
>
> .
>

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


"Robbro" <(E-Mail Removed)> wrote in message
news:BA4977F9-7DA8-4389-80CE-(E-Mail Removed)...
> 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.
> "T. Valko" wrote:
>
>> 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
>>
>>
>> "Robbro" <(E-Mail Removed)> wrote in message
>> news:B1A70A43-35D7-416F-BBDB-(E-Mail Removed)...
>> >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" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:E769288B-779B-488F-870D-(E-Mail Removed)...
>> >> > 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?
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

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.

"T. Valko" wrote:

> 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
>
>
> "Robbro" <(E-Mail Removed)> wrote in message
> news:BA4977F9-7DA8-4389-80CE-(E-Mail Removed)...
> > 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.
> > "T. Valko" wrote:
> >
> >> 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
> >>
> >>
> >> "Robbro" <(E-Mail Removed)> wrote in message
> >> news:B1A70A43-35D7-416F-BBDB-(E-Mail Removed)...
> >> >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" wrote:
> >> >
> >> >> 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" <(E-Mail Removed)> wrote in message
> >> >> news:E769288B-779B-488F-870D-(E-Mail Removed)...
> >> >> > 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?
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>

OK, good luck!

--
Biff
Microsoft Excel MVP


"Robbro" <(E-Mail Removed)> wrote in message
news:E2908CEB-9D33-469B-B7A6-(E-Mail Removed)...
> 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.
>
> "T. Valko" wrote:
>
>> 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
>>
>>
>> "Robbro" <(E-Mail Removed)> wrote in message
>> news:BA4977F9-7DA8-4389-80CE-(E-Mail Removed)...
>> > 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.
>> > "T. Valko" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "Robbro" <(E-Mail Removed)> wrote in message
>> >> news:B1A70A43-35D7-416F-BBDB-(E-Mail Removed)...
>> >> >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" wrote:
>> >> >
>> >> >> 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" <(E-Mail Removed)> wrote in message
>> >> >> news:E769288B-779B-488F-870D-(E-Mail Removed)...
>> >> >> > 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?
>> >> >>
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>