Taking the value of one cell based on the information on an adjacent cell.

**cousinsd88** · 11-28-2010, 05:06 AM

Hi Guys,

Im a complete new member and at best I am mediocre so I assume I will be bothering you quite abit! I need a formulae that will effectivley the value of cells that fall within certain dates.

eg.. if the dates in colum F are between 1/1/2010 and the 31/12/2010 then add up the data in colum D that are next to these dates.

I have attached a sheet that I hopes makes this more clear.

Thanks

**DonkeyOte** · 11-28-2010, 05:15 AM

A few approaches in truth.

=SUMPRODUCT((TEXT(F2:F64,"yyyy")="2010")+0,D2:D64)

or if you know F2:F64 will always and only contain numbers (dates) [not the case here] then

=SUMPRODUCT((YEAR(F2:F64)=2010)+0,D2:D64)

or if you will not always be looking for a given year but variable dates (eg Jan-Jun) then:

=SUMPRODUCT((F2:F64>=DATE(2010,1,1))*(F2:F64<=DATE(2010,6,30)),D2:D64)

SUMPRODUCT is not overly efficient so you could also consider using 2 SUMIFs (or SUMIFS XL2007+)

=SUM(SUMIF(F2:F64,{">=01/01/2010",">=01/01/2011"},D2:D64)*{1,-1})

Finally... and not least... you might want to consider using a Pivot Table if the analysis is based on years etc (you can group the Date Field by Year) but again this would require only numbers in column F (not presently the case)

**cousinsd88** · 11-29-2010, 07:08 AM

Your an absolute lad! Cheers

**mahju** · 11-29-2010, 10:28 AM

Select the required cell for example I19
enter the formula

=IF(AND(F19>=DATE(2010,1,1),F19<=DATE(2010,12,31)),D20+D21+D22,"---")

If value in F2 is between 01/01/2010 and 12/31/2010 it will add next three values in column D other wise write "----" there
copy this formula to all the column

You have to change the formula for every year