SUMIF and VLookup/Index match for consolidating Data

[brees41]

Hello,

I'm trying to make life a little more efficient for some of our accountants but have hit an excel brick wall.

Background: We consolidate about 7 company's financials into one consolidated financial statement. For reasons unknown, these financials are not in a standardized format so I am constantly linking and relinking cells each month. It takes about 1-2 days total, let alone if there are any revision. Here's the key issue:

In excel, the accountants will provide me multiple accounts that can be consolidated into 1. For example:

In Raw excel sheet from Accountants (Sheet A)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Cash on Hand[/TD]
[TD]$1,000.00[/TD]
[/TR]
[TR]
[TD]Cash in the Bank[/TD]
[TD]$500.00[/TD]
[/TR]
[TR]
[TD]Cash in Securities[/TD]
[TD]$150.00[/TD]
[/TR]
</tbody>[/TABLE]

What I'd like to do is something SUM all these figures with a criteria table in another sheet. For Example:

My excel sheet/working tab (Sheet B)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Table B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cash on Hand[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Cash in the Bank[/TD]
[TD]Cash[/TD]
[/TR]
[TR]
[TD]Cash in Securities[/TD]
[TD]Cash[/TD]
[/TR]
</tbody>[/TABLE]

And then finally, my financial standardized financial Statement

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cash[/TD]
[TD]$1,650.00[/TD]
[/TR]
</tbody>[/TABLE]


Ideally with a formula like: =SUMIF(Vlookup(Sheet A Column A,Table B,2,False),="CASH",Sheet A Column B)

Is this possible with a formula? Currently I am creating an excel sheet that extracts the raw data and then I add my own columns, except as I said before these rows/columns always change.

Currently what I am doing:

Month 1 - Classification Sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Cash on Hand[/TD]
[TD]$1,000.00[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Cash in the Bank[/TD]
[TD]$500.00[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Cash in Securities[/TD]
[TD]$150.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Month 2 - Classification Sheet

(Assume values are the same and I don't reclassify it)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Fixed Assets[/TD]
[TD]$10,000.00[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Cash in the Bank[/TD]
[TD]$500.00[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD]Cash in Securities[/TD]
[TD]$150.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I am trying to avoid is every month having to spend 5 hours reclassifying the accounts. Instead, just using a formula that references the account name (Column B) and sums it automatically.

Much help with be appreciated.

 

[DRSteele]

Welcome to the forum.

Well, I think you need a table of accounts and knowledge about several Excel functions like SUMIFS. All that linking and manual pointing every month is a sinful waste of time.

The use of the official ExcelTable functionality is the key to your solution. This is by no means exactly what you need, but see if it sparks any insight:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.excel-university.com/articles/journal-of-acct/the-power-of-mapping/[/FONT]

 

[brees41]

Welcome to the forum.

Well, I think you need a table of accounts and knowledge about several Excel functions like SUMIFS. All that linking and manual pointing every month is a sinful waste of time.

The use of the official ExcelTable functionality is the key to your solution. This is by no means exactly what you need, but see if it sparks any insight:
https://www.excel-university.com/articles/journal-of-acct/the-power-of-mapping/

I am familiar with SUMIFS to a certain extent, but even with a SUMIF I still need the criteria to realign based on the accounts given by the accountants. Is there a way to have a sumif accomplishes the issue above?

Something like SUMIF(Column A Sheet A, Vlookup(Column A Sheet A, Column A Sheet B, 2, False)="CASH",SUMRANGE<>)?