sum from reference between dates on another sheet

[IanWells]

hi all

please help...

I need to sum values using a reference cell and between dates, here is hopefully the guide.

sheet 1 contains all data, column 'H' contains reference number, column 'AB' contain value, column 'M' contains dates.

sheet 2 for formula, in column 'A' will contain reference to coincide with(sheet 1 'H' col)
in column 'B' formula like the following

=sum 'sheet1!AB2:AB13638' if 'sheet2!H2:H13638' same as cell 'sheet1!A1' between 01/01/2010 to 01/08/2011 in 'sheet1!M2:M13638'

Many thanks for your time

Ian

 

[Yahya]

try this arrayformula

=SUM(IF(Sheet1!$H$2:$H$10=Sheet2!A2,IF(Sheet1!$M$2:$M$10>="1/1/2010",IF(Sheet1!$M$2:$M$10<="1/8/2010",Sheet1!$AB$2:$AB$10))))

must entered with Ctrl+Shift+Enter

 

[IanWells]

WOW how long is that! well i must say after a little tweaking to suit ranges and using a cell reference for the date it is just what i need

Thank you very much.

Ian

 

[Yahya]

WOW how long is that! well i must say after a little tweaking to suit ranges and using a cell reference for the date it is just what i need

Thank you very much.

Ian

you are welcome

 

[grizle]

Here's an alternative:
=SUMPRODUCT(--(Sheet1!H2:H13638=Sheet2!$A$1),--(Sheet1!M2:M13638>=DATEVALUE("1/1/10")),--(Sheet1!M2:M13638<=DATEVALUE("1/8/11")),Sheet1!AB2:AB13638)