Lookup function using 2 variables
 

I am trying to create a formula to do the following:

I have a data sheet that contains cost information. The information
contains a project number and an account number. I want a "nested lookup"
type of function that says, "If the project number equals X and the account
number equals y, give me the sum of the records. I have a sheet for each
project and the project number is in a cell that will be referenced in the
formula. Multiple account numbers are in each project sheet and those will
be used as references, also. The goal is to set up formulas that use lookup
functionality so I can import a data sheet every month and just do a mass
search and replace for the new sheet name in the formula, but no formula
adjustments are needed. I have experimented with sumif, index, nested If
statements, combinations using sum with and functions, etc. I haven't yet
found the answer, but I'm sure someone has. Any help would be appreciated.

Lookup function using 2 variables
 

One way:

=SUMPRODUCT(--(proj_num_rng="X"),--(acct_num_rng="Y"),value_rng)


In article ,
tbar38547 wrote:

I am trying to create a formula to do the following:

I have a data sheet that contains cost information. The information
contains a project number and an account number. I want a "nested lookup"
type of function that says, "If the project number equals X and the account
number equals y, give me the sum of the records. I have a sheet for each
project and the project number is in a cell that will be referenced in the
formula. Multiple account numbers are in each project sheet and those will
be used as references, also. The goal is to set up formulas that use lookup
functionality so I can import a data sheet every month and just do a mass
search and replace for the new sheet name in the formula, but no formula
adjustments are needed. I have experimented with sumif, index, nested If
statements, combinations using sum with and functions, etc. I haven't yet
found the answer, but I'm sure someone has. Any help would be appreciated.

Lookup function using 2 variables
 

Hi tbar38547:

The formula proposed by JE McGimsey will give you the total cost where
Project_No = "X" and Account_No = "Y"

if you simply want the count where Project_No = "X", and Account_No = "Y",
you may use ...

=SUMPRODUCT((proj_num_rng="X")*(acct_num_rng="Y"))




"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(proj_num_rng="X"),--(acct_num_rng="Y"),value_rng)


In article ,
tbar38547 wrote:

I am trying to create a formula to do the following:

I have a data sheet that contains cost information. The information
contains a project number and an account number. I want a "nested lookup"
type of function that says, "If the project number equals X and the account
number equals y, give me the sum of the records. I have a sheet for each
project and the project number is in a cell that will be referenced in the
formula. Multiple account numbers are in each project sheet and those will
be used as references, also. The goal is to set up formulas that use lookup
functionality so I can import a data sheet every month and just do a mass
search and replace for the new sheet name in the formula, but no formula
adjustments are needed. I have experimented with sumif, index, nested If
statements, combinations using sum with and functions, etc. I haven't yet
found the answer, but I'm sure someone has. Any help would be appreciated.