I have the following:

Sheet 1 = Column A = account number (ex. 675000) = lookup_value

Sheet 2 = Column A-G subtotaled for Colunm A, by account = Table_array

Col_index # is for G (7)

My problem, sheet 1 column A is only the account #.

How can I have it find in sheet 2 the account subtotals?

Its something to do with my sheet 1 account number since it doesnt match
the table array €œaccount # total€� on sheet 2 for the lookup.

Thanks

It sounds like you are trying to use VLOOKUP to find the subtotals for each account number in Sheet 1, but the account numbers in Sheet 1 do not match the account numbers in the table array on Sheet 2.

To solve this issue, you can use a combination of the VLOOKUP and SUMIF functions. Here are the steps:

1. In Sheet 1, create a new column next to the account numbers column. In this new column, use the SUMIF function to sum the values in Sheet 2 for each account number. The formula should look something like this:
   
   
   Code:

   =SUMIF(Sheet2!A:A,A2,Sheet2!G:G)

   This formula will sum all the values in column G of Sheet 2 where the account number in column A of Sheet 2 matches the account number in cell A2 of Sheet 1.
   
2. Once you have the subtotals for each account number in Sheet 1, you can use VLOOKUP to look up the subtotals based on the account number. The formula should look something like this:
   
   
   Code:

   =VLOOKUP(A2,Sheet1!A:B,2,FALSE)

   This formula will look up the value in cell A2 (the account number) in column A of Sheet 1, and return the corresponding value in column B (the subtotal).
   

By using these two functions together, you should be able to find the subtotals for each account number in Sheet 1, even if the account numbers do not match exactly between the two sheets.

It's always useful to post the formula you have. I imagine it will
look like this:

=VLOOKUP(A1,Sheet2!A:G,7,0)

If you only want to get the subtotal row, then use this:

=VLOOKUP(A1&" Total",Sheet2!A:G,7,0)

Hope this helps.

Pete

On Aug 31, 7:10*pm, deco wrote:
I have the following:

Sheet 1 = Column A = account number (ex. 675000) = lookup_value

Sheet 2 = Column A-G subtotaled for Colunm A, by account = Table_array

Col_index # is for G (7)

My problem, sheet 1 column A is only the account #.

How can I have it find in sheet 2 the account subtotals?

It’s something to do with my sheet 1 account number since it doesn’t match
the table array “account # total” on sheet 2 for the lookup.

Thanks

Hi,
you can get the subtotal for each account from a sheet with the information
without the subtotals with the formula as follow in sheet1 column B

=sumproduct(--(A1=sheet2!$A$1:$A$1000),sheet2!$B$1:$B$1000)

I assumed that the column to summarize is column B from sheet2



"deco" wrote:

I have the following:

Sheet 1 = Column A = account number (ex. 675000) = lookup_value

Sheet 2 = Column A-G subtotaled for Colunm A, by account = Table_array

Col_index # is for G (7)

My problem, sheet 1 column A is only the account #.

How can I have it find in sheet 2 the account subtotals?

Its something to do with my sheet 1 account number since it doesnt match
the table array €œaccount # total€� on sheet 2 for the lookup.

Thanks

Thank you Pete, I'll remember to post my formula next time around.

The =VLOOKUP(A1&" Total",Sheet2!A:G,7,0) did the trick.

Thanks for your help

"Pete_UK" wrote:

It's always useful to post the formula you have. I imagine it will
look like this:

=VLOOKUP(A1,Sheet2!A:G,7,0)

If you only want to get the subtotal row, then use this:

=VLOOKUP(A1&" Total",Sheet2!A:G,7,0)

Hope this helps.

Pete

On Aug 31, 7:10 pm, deco wrote:
I have the following:

Sheet 1 = Column A = account number (ex. 675000) = lookup_value

Sheet 2 = Column A-G subtotaled for Colunm A, by account = Table_array

Col_index # is for G (7)

My problem, sheet 1 column A is only the account #.

How can I have it find in sheet 2 the account subtotals?

Its something to do with my sheet 1 account number since it doesnt match
the table array €œaccount # total€� on sheet 2 for the lookup.

Thanks

You're welcome - thanks for feeding back.

Pete

On Aug 31, 7:53*pm, deco wrote:
Thank you Pete, I'll remember to post my formula next time around. *

The =VLOOKUP(A1&" Total",Sheet2!A:G,7,0) did the trick.

Thanks for your help



"Pete_UK" wrote:
It's always useful to post the formula you have. I imagine it will
look like this:

=VLOOKUP(A1,Sheet2!A:G,7,0)

If you only want to get the subtotal row, then use this:

=VLOOKUP(A1&" Total",Sheet2!A:G,7,0)

Hope this helps.

Pete

On Aug 31, 7:10 pm, deco wrote:
I have the following:

Sheet 1 = Column A = account number (ex. 675000) = lookup_value

Sheet 2 = Column A-G subtotaled for Colunm A, by account = Table_array

Col_index # is for G (7)

My problem, sheet 1 column A is only the account #.

How can I have it find in sheet 2 the account subtotals?

It’s something to do with my sheet 1 account number since it doesn’t match
the table array “account # total” on sheet 2 for the lookup.

Thanks- Hide quoted text -

- Show quoted text -

Hi,

Here is another approach

=SUMIF(Sheet2!A1:A99,A1,G1:G99)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"deco" wrote:

I have the following:

Sheet 1 = Column A = account number (ex. 675000) = lookup_value

Sheet 2 = Column A-G subtotaled for Colunm A, by account = Table_array

Col_index # is for G (7)

My problem, sheet 1 column A is only the account #.

How can I have it find in sheet 2 the account subtotals?

Its something to do with my sheet 1 account number since it doesnt match
the table array €œaccount # total€� on sheet 2 for the lookup.

Thanks