ExcelBanter - SUMPRODUCT DUPLICATES

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SUMPRODUCT DUPLICATES (https://www.excelbanter.com/excel-worksheet-functions/207387-sumproduct-duplicates.html)

SUMPRODUCT DUPLICATES

I am having issues with duplicating lines of info i am currently using a
SUMPRODUCT and and INDEX function but can n ot seem to get it to work
acurately.

Current Data
EQ# Cost Code Hours
1 2.2.200
1 3.2.710 4
1 3.4.700 3
1 3.4.710 5
1 3.4.710 6
1 3.4.710 5
1 3.4.710 2
1 3.4.710 2
1 3.4.710 2
1 3.4.710 8
1 3.4.710 7
1 3.4.710 8
1 3.4.710 7
1 3.4.710 6
1 3.4.710 4
1 3.4.710 6
1 5.2.100 5

I would like to go to a second sheet put in 1 eq number and have it populate
the cost codes and sum of hours within those cost codes without duplicating
cost codes and hours.

SUMPRODUCT DUPLICATES

Hi,

I'm struggling to understand the relevance of 1 in the EQ# column because
everything is 1. To sum cost codes from another sheet simply use

=SUMPRODUCT((Sheet1!B1:B17="3.4.710")*(Sheet1!C1:C 17))

In practice I'd use a cell reference for "3.4.710"

Mike

"GSemler" wrote:

I am having issues with duplicating lines of info i am currently using a
SUMPRODUCT and and INDEX function but can n ot seem to get it to work
acurately.

Current Data
EQ# Cost Code Hours
1 2.2.200
1 3.2.710 4
1 3.4.700 3
1 3.4.710 5
1 3.4.710 6
1 3.4.710 5
1 3.4.710 2
1 3.4.710 2
1 3.4.710 2
1 3.4.710 8
1 3.4.710 7
1 3.4.710 8
1 3.4.710 7
1 3.4.710 6
1 3.4.710 4
1 3.4.710 6
1 5.2.100 5

I would like to go to a second sheet put in 1 eq number and have it populate
the cost codes and sum of hours within those cost codes without duplicating
cost codes and hours.

SUMPRODUCT DUPLICATES

Use Excel 2007 PivotTable.
No formulas needed.
http://www.savefile.com/files/1853571