sum up the unique column values according the condition operator with other cells

[ramminani]

hi,

i have a sheet having car number (D column),year of purchase (E column), accumulated at end of year (I column) and total cost (Q column)..

my problem is for example the car number 12 ...

i need to get the value in one cell like this ....

sum up the I column according to year ,car number and the sum up value should be less than total cost(Q column). if it is greater than total cost then need to keep total value....

problem is the cell range or cell address don't know. they are generated.

how to do that.... can any one help me please


New Microsoft Office Excel Worksheet.xlsx

[CostCare]

Hi,

with "need to keep total value" you mean the total value of the sum of column I?

Reagrds

[skywriter]

You could try this formula. D24 is one of your cells with 12 in it. You could put 12 directly into the formula instead of D24 or you could reference another cell where you type in the car number of your choice.

Please Login or Register  to view this content.

[CostCare]

Hi,

Maybe this function I made for you can help you:

Please Login or Register  to view this content.

Copy this to a module in VBA and you can use it the same way you use regular formulas. It needs carnumber, year, SearchRange (1st column of range should contain carnumber, 2nd column year and 3rd column accumulated) and totalCosts.

Regards,

Bart