Lookup value
 

Hi,

Look up value is a date

If lookup value in Sheet1 is cell C2 = 28/3/2005
then sum up the value of range from cell A1 to A10 in Sheet2 when the
lookup value in Sheet1 is matched against the date from cell C1 to
C110
Thus, the result should be 20,490.20 ( ie sum up cell A3,A7 to A10 when
cell C3,C7 to C10 are matched with the date 28/3/2005 )
Sheet2
A B C
1 9,006.30 100050 01/03/2005
2 100.00 100050 01/03/2005
3 144.00 138616 28/03/2005
4 20.00 600045 24/03/2005
5 7,215.30 100001 25/03/2005
6 2,000.00 600139 25/03/2005
7 906.00 155661 28/03/2005
8 16,754.00 138615 28/03/2005
9 2,560.00 138617 28/03/2005
10 126.20 138618 28/03/2005

How to set a formula or VBA code to arrive the above result ?

Please help, Thanks
Regards
Len

Lookup value
 

Enter the following into the required cell in Sheet2 and press
Shift+Ctrl+Enter to enter as an array formula

=SUM((C1:C10=Sheet1!C2)*(A1:A10))

This will appear as

{=SUM((C1:C10=Sheet1!C2)*(A1:A10))}

HTH

"Len" wrote:

Hi,

Look up value is a date

If lookup value in Sheet1 is cell C2 = 28/3/2005
then sum up the value of range from cell A1 to A10 in Sheet2 when the
lookup value in Sheet1 is matched against the date from cell C1 to
C110
Thus, the result should be 20,490.20 ( ie sum up cell A3,A7 to A10 when
cell C3,C7 to C10 are matched with the date 28/3/2005 )
Sheet2
A B C
1 9,006.30 100050 01/03/2005
2 100.00 100050 01/03/2005
3 144.00 138616 28/03/2005
4 20.00 600045 24/03/2005
5 7,215.30 100001 25/03/2005
6 2,000.00 600139 25/03/2005
7 906.00 155661 28/03/2005
8 16,754.00 138615 28/03/2005
9 2,560.00 138617 28/03/2005
10 126.20 138618 28/03/2005

How to set a formula or VBA code to arrive the above result ?

Please help, Thanks
Regards
Len

Lookup value
 

Hi Toppers,

Thanks, it works

Regards
Len

Toppers wrote:
Enter the following into the required cell in Sheet2 and press
Shift+Ctrl+Enter to enter as an array formula

=SUM((C1:C10=Sheet1!C2)*(A1:A10))

This will appear as

{=SUM((C1:C10=Sheet1!C2)*(A1:A10))}

HTH

"Len" wrote:

Hi,

Look up value is a date

If lookup value in Sheet1 is cell C2 = 28/3/2005
then sum up the value of range from cell A1 to A10 in Sheet2 when
the
lookup value in Sheet1 is matched against the date from cell C1 to
C110
Thus, the result should be 20,490.20 ( ie sum up cell A3,A7 to A10
when
cell C3,C7 to C10 are matched with the date 28/3/2005 )
Sheet2
A B C
1 9,006.30 100050 01/03/2005
2 100.00 100050 01/03/2005
3 144.00 138616 28/03/2005
4 20.00 600045 24/03/2005
5 7,215.30 100001 25/03/2005
6 2,000.00 600139 25/03/2005
7 906.00 155661 28/03/2005
8 16,754.00 138615 28/03/2005
9 2,560.00 138617 28/03/2005
10 126.20 138618 28/03/2005

How to set a formula or VBA code to arrive the above result ?

Please help, Thanks
Regards
Len