Excel 2016 - Find matching rows in column b, then sum Column C copy sum in Next Sheet Column A

0
votes

I have a worksheet with the following 3 columns on Sheet 1:

ID  Week#   CodeCreateTime
16972   39  4
16980   39  4
19117   39  2
19146   40  2
22362   41  10
20890   41  5

I want to:

  1. Find all items that match in the 'Week#' column

  2. Sum their values in the 'CodeCreateTime' column

  3. Copy the week# and summed value onto Sheet 2 in Column A.

For example, using the above data from Sheet 1, Sheet 2 would have the following values in column A and B:

A       B
39      10
40      2
41      15

Will a sumif(match) work? I have been playing and not having luck.

1
mysqlvbaexcel
The SUMIF function alone will already do the trick. – VBA Pete
something like this : =SUMIF(Sheet1!$B$1:$B$6,Sheet2!A1,Sheet1!$C$1:$C$6) – cyboashu
=SUMIF(B:B,39,C:C) would be an example for week 39 if you week is column B and your CodeCreateTime column is in column C. – VBA Pete
You could also create a Pivot Table, although it is not dynamic like formulas. – Ron Rosenfeld
@RonRosenfeld - PivotTable could be made dynamic by basing it's data source on a dynamic named range :) – Scott Holtzman

1 Answers

0
votes

1) Copy Sheet1!B1:B6 to Sheet2!A1:A6
2) Remove Duplicates on Sheet2!A1:A6
3) In sheet2!B2 type: =SUMIF(Sheet1!$B$2:$B$7,A2,Sheet1!$C$2:$C$7)
4) Drag down rest of rows in Sheet2