Expenses Data Formula Reqd

[webmax]

Hi

I have a data of car purchased. i want the formula to find how much the amount is spend in the 1st year, and in the 2nd year and third year

For example if the car is purchase in the 01st Jan 10
The First of completion is 31st Dec 2010. I want how much the amount is spent in the period 01st Jan 10 to 31st Dec 2010
The same like in the second year from 01st Jan 11 to 31st Dec 2011 like third year etc.

The First table contains the purchase date and 1st year, 2nd year and 3rd year
The Second table contains the amount spent (Transactions)
I need the output in the third table

Regards
Shahuls

 

[Somendra Misra]

Hi Webmax,

In C21 use below formula and copy right and down.

=SUMPRODUCT(($B21=$B$9:$B$16)*($D$9:$D$16)*($C$9:$C$16>=INDEX($C$2:$F$4,MATCH($B21,$B$2:$B$4,0),COLUMNS($C21:C21)))*($C$9:$C$16<=INDEX($D$2:$F$4,MATCH($B21,$B$2:$B$4,0),COLUMNS($C21:C21))))

Just advise if any issue.

Regards,

 

[webmax]

Hi

Kindly put the formula in the output sheet i am unable to get that

 

[Somendra Misra]

@webmax

See the attached. Please note you may have to extend the range inside MATCH function lookup vector argument as per your data range. Right now it has only one cell ref.

If you had any trouble in doing this, write back.

Regards,

 

[webmax]

Hi

Can you help if i put the additional data i am unable to do. can put the formula if i put the entire data

Regards
Shahul

 

[Somendra Misra]

Hi Webmax,

One question, Any expense done on date of purchase will be counted in first year or it will not be counted , I mean normally the expense the vehicle is counted from the next day just clarify it.

Regards,

 

[webmax]

yes the expense will count from next day up to completion of 1st year will come in the 1st year. this is report is find how much spend in the first year, second year etc....

 

[Somendra Misra]

See the attached. Do note the ranges used in the formula you may have to change as per your data.

Regards,

 

[webmax]

Hi

This the error and data not coming

I am now attaching the full list

can u please clarify why you pointing out the COLUMNS($C21:C21)

 

[Somendra Misra]

@webmax

See the attached, if this is OK, than please read my above comment, you have to adjust the range as per your data.

Regards,

 

[webmax]

can you explain about the formula
I did not understad about the COLUMNS($C21:C21))))

=IFERROR(SUMPRODUCT(($A2=Expenses!$A$2:$A$278)*(Expenses!$C$2:$C$278)*(Expenses!$B$2:$B$278>INDEX(Data!$C$2:$H$46,MATCH(Output!$A2,Data!$A$2:$A$46,0),COLUMNS($B2:B2)))*(Expenses!$B$2:$B$278<=INDEX(Data!$C$2:$H$46,MATCH(Output!$A2,Data!$A$2:$A$46,1),COLUMNS($C21:C21)))),"-")

 

[Somendra Misra]

@webmax

If you see in your year start date can be C2:G46 in data sheet and end date can be D2:H46, so as you copy the formula right it should increment by 1 so COLUMNS function is generating a series of sequential number 1,2,3,4,5 for getting the start and end date of year from different columns.

Regards,

 

[webmax]

Hi Somendra
The Calculation is showing wrong.
For the Code 40102286 the total expenses is Rs. 40146/- but in the first year it is wrongly showing as 40146/- but the correct amount is 2468/- for second year 20357/- and third year 17321/-

 

[Somendra Misra]

Hi,

OOPS...I did a mistake, please use below formula.

=IFERROR(SUMPRODUCT(($A2=Expenses!$A$2:$A$278)*(Expenses!$C$2:$C$278)*(Expenses!$B$2:$B$278>INDEX(Data!$C$2:$H$46,MATCH(Output!$A2,Data!$A$2:$A$46,0),COLUMNS($B2:B2)))*(Expenses!$B$2:$B$278<=INDEX(Data!$D$2:$H$46,MATCH(Output!$A2,Data!$A$2:$A$46,0),COLUMNS($C21:C21)))),"-")

I changed the red 0 to 1. Put this formula in B2 and copy right and down on output sheet.

Regards,