Excel - SUM based on matching from another sheet

0
votes

I have two sheets.

SHEET1 has a NAME and a DATE column.

SHEET2 has NAME, DATE and TOTAL column.

SHEET1 has unique names and dates (might be more than one NAME, but not more than one NAME with same DATE).

SHEET2 has a bunch of multiple names and dates with totals.

EXAMPLE:

SHEET1 

NAME        DATE
Todd        3/12/2013
Andy        3/15/2013
Bill        11/11/2013
Paul        3/10/2014

SHEET2 

NAME        DATE            TOTAL
Todd        3/12/2013       $10
Todd        3/12/2013       $15
Todd        3/19/2013       $12
Bill        11/11/2013      $10
Bill        11/15/2013      $12
ANDY        3/15/2013       $15
Paul        3/25/2014       $20

I need to be able to total per month and year IF SHEET1 matches SHEET2. In the example above, it would total like this:

March 2013 - $40 (Paul and the last Todd would not count because the date in SHEET2 for Todd does not match the NAME and DATE for Todd in SHEET1)

November 2013 - $10 (Again, the last Bill total on SHEET2 would not count because the date does not match Bill on SHEET1).

Any ideas?

1
excel

1 Answers

1
votes

I assume that the tables start in A1 in the corresponding sheet and the sheets are calles Sheet1,Sheet2 (you may have to adopt the formulas)

Sheet2

NAME**********DATE************TOTAL*********IsValid
Todd**********3/12/2013*******$10***********=COUNTIFS(Sheet1!$A:$A,A2,Sheet2!$B:$B,B2)>0
Todd**********3/12/2013*******$15***********=COUNTIFS(Sheet1!$A:$A,A3,Sheet2!$B:$B,B3)>0
...

COUNTIFS(Sheet1!$A:$A,A2,Sheet2!$B:$B,B2) counts the number of rows where in sheet1 column A it equals (sheet2) A2 and also in sheet1 column B it equals (sheet2) B2

Sheet3

DATE***********TOTAL
1/1/2013*******=SUMIFS(Sheet2!$C:$C,Sheet2!$D:$D,true,Sheet2!$B:$B,">=" & A2,Sheet2!$B:$B,"<" & DATE(YEAR(A2),MONTH(A2)+1,1))
2/1/2013*******=SUMIFS(Sheet2!$C:$C,Sheet2!$D:$D,true,Sheet2!$B:$B,">=" & A3,Sheet2!$B:$B,"<" & DATE(YEAR(A3),MONTH(A3)+1,1))
...

SUMIFS(Sheet2!$C:$C,Sheet2!$D:$D,true,Sheet2!$B:$B,">=" & A2,Sheet2!$B:$B,"<" & DATE(YEAR(A2),MONTH(A2)+1,1)) sums all cells in Sheet2!$C:$C where in the same row in column D equals true, in column B equals greater A2 and in column B is lower than the start of next month of A2