• 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.

Expenses Data Formula Reqd

W

webmax

Member
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
 

Attachments

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

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,
 

Attachments

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,
 
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....
 
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)
 

Attachments

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)))),"-")
 
@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,
 
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/-
 
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,
 
You must log in or register to reply here.
Back
Top