Consolidation of statement

[Sarfraz Khan]

Dear Sir,

I have two P&L sheets once is TPL and onther one is SPL.

I have consolidated both in CPL called consolidated P&L using sum if but I am not getting final amount tallied with both statement.

Total of TPL+Total of SPL should be equal to Total CPL but its not getting tallied, I want to know which amounts are missing using formula.

please advice.

 

[srinidhi]

Your major Direct Expenses
Freight Paid A/C 13,570.16
Freight Paid A/C 608.67
is missing in CPL sheet

 

[Sarfraz Khan]

Your major Direct Expenses
Freight Paid A/C 13,570.16
Freight Paid A/C 608.67
is missing in CPL sheet

Thanks for the same, but can you please provide the solution how to find that.

 

[Khalid NGO]

Hi Sarfraz,

I think there are additional space in some of your cells:
add a TRIM in your existing formula, like this:

=SUMIF(TPL!A:A,"*" &TRIM(A5)& "*",TPL!C:C)+SUMIF(SPL!A:A,"*" &TRIM(A5)& "*",SPL!B:B)

Regards,

 

[Sarfraz Khan]

Hi Sarfraz,

I think there are additional space in some of your cells:
add a TRIM in your existing formula, like this:

=SUMIF(TPL!A:A,"*" &TRIM(A5)& "*",TPL!C:C)+SUMIF(SPL!A:A,"*" &TRIM(A5)& "*",SPL!B:B)

Regards,

Dear Sir,
Thanks for the same, but still its not getting fully matched, I think still items are missing or some items are coming twice, is there any way where I can Audit it instead of checking one by one.

 

[Khalid NGO]

As you have used CF for duplicate text.
I found:
Amount in TPL sheet for "Staff Welfare "

whereas you are looking for the string "Staff Welfare Expenses"

This or like this could be the reason.

 

[Khalid NGO]

While doing sorting A to Z
I found some duplicate entries (plus miss-spelled)

Bad Debt
Bad Debts

Business Promotion
Business Promotion Expenses

Delivery Expenses
Delivey Expenses

Membership & Subscription
Membership And Subscription

Printing & Stationary
Printing & Stationery Expenses

Repairs & Maintenance
Repairs & Maintenance Expenses

Staff Welfare
Staff Welfare Expenses

 

[Sarfraz Khan]

While doing sorting A to Z
I found some duplicate entries (plus miss-spelled)

Bad Debt
Bad Debts

Business Promotion
Business Promotion Expenses

Delivery Expenses
Delivey Expenses

Membership & Subscription
Membership And Subscription

Printing & Stationary
Printing & Stationery Expenses

Repairs & Maintenance
Repairs & Maintenance Expenses

Staff Welfare
Staff Welfare Expenses

Its tallied now.

 

[Khalid NGO]

Its tallied now.

Great...

Enjoy dear.