How to get data from other sheet using date

2
votes

i have sheet1,

Name    Date    Amount
Ali     1-Sep   50
Ali     2-Sep   100
Ali     5-Sep   30
        13-Sep  40
        9-Aug   50
        25-Sep  60

and sheet2, 

Name    Date    Amount
Ali     1-Sep   100
Ali     2-Sep   100
Ali     5-Sep   85
Ali     13-Sep  34
Ali     9-Aug   88
Ali     25-Sep  25

on sheet 3, how do i search both sheet1 and sheet2 using dates, get the amount from both sheets and sum both amount on sheet3

output : 

Nama    Tarikh  Total
Ali     1-Sep   150
Ali     2-Sep   200
        3-Sep   
        4-Sep   
        5-Sep   115
        6-Sep   
        7-Sep   
        8-Sep   
        9-Sep   138
        10-Sep  
        11-Sep  
        12-Sep  
        13-Sep  74

thank you in advance

2
excelexcel-formula

2 Answers

1
votes

You can use the VLOOKUP function to get the total. Enter the following formula into cell C2 in Sheet3:

=IFERROR(VLOOKUP(B2, Sheet1!$B$2:$C$7, 2, 0) + VLOOKUP(B2, Sheet2!$B$2:$C$7, 2, 0), 0)

enter image description here

This assumes that your data in sheets 1 and 2 are structured as follows:

    A       B         C
1 Name  |  Date  |  Amount
2  Ali  |  1-Sep |    50
3  Ali  |  2-Sep |   100
4  Ali  |  5-Sep |    30
5       | 13-Sep |    40
6       |  9-Aug |    50
7       | 25-Sep |    60
0
votes

You can use =Sheet1!A1 and =Sheet2!A1 on Sheet3.

In general: use "=[SheetName]![CellName].

To see this easier: type '=' in the required ceel in sheet3 and click another sheet to chose the required cell. Excel will do the job for you