• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data format

Shabbo

Member
Dear Sir,
I wanted to convert Garage Expenses data to as per Consolidated sheet.
Please advise.
 

Attachments

  • Vehicle Report Consollidate for excel.xlsx
    110.2 KB · Views: 5
Shabbo, Good afternoon.

You can use:
Consolidated

B6
--> =SUMPRODUCT( [B]([/B]MONTH('Garage Expenses'!B2:B5000)=MONTH($B$2)[B]) * ([/B]'Garage Expenses'!C2:C5000=A6[B]) * ([/B]'Garage Expenses'!D2:D5000[B])[/B])

Attention:
In the Garage Expenses line 1700 there is a header line which invalidates all calculations.

Delete it and everything will work.

Is this what you want?

I hope it helps.
 
Last edited by a moderator:
Shabbo,

To avoid having the same vehicle in the same months in several years, use:

Consolidated

as above >>> use Inline code <<<
B6
--> =SUMPRODUCT((MONTH('Garage Expenses'!$B$2:$B$4034)&YEAR('Garage Expenses'!$B$2:$B$4034) = MONTH(B2)&YEAR(B2)) * ('Garage Expenses'!$C$2:$C$4034=A6) * ('Garage Expenses'!$D$2:$D$4034))

I hope it helps.
 
Last edited by a moderator:
And why not a nice and easy Pivot Table instead of complicated formulas??
Your data as the perfect layout
First delete row 1700 containing unneeded text
Remove the existing filter
Select your range and create an Excel Table
Use dates as row labels and group as required ( Months and years)
Use vehicles as column labels
And the Debit Amount as Values
You are now all set to explore the wonderful world of Pivot tables
( as the range is now an Excel Table, you can add or delete rows, the PT will adapt after refreshing
 

Attachments

  • Copy of Vehicle Report Consollidate for excel.xlsx
    155.1 KB · Views: 2
Back
Top