### Question:

I have a long table The key is actually col B&C BUT…sometime there are few rows with same key (like rows 3:4 or rows 8:10). I'd like to sum data in column D and to consider same key rows as one row.

Desired result 216

Can I also add condition that Column E=1 ?

### Formula in cell E21:

=SUMPRODUCT(--(\$E\$3:\$E\$18=\$C\$21), \$D\$3:\$D\$18, 1/COUNTIFS(\$B\$3:\$B\$18, \$B\$3:\$B\$18, \$C\$3:\$C\$18, \$C\$3:\$C\$18, \$D\$3:\$D\$18, \$D\$3:\$D\$18, \$E\$3:\$E\$18, \$E\$3:\$E\$18))

This formula removes duplicate records and sums values in col D.

### Explaining the formula in cell E21

To simplify the explanation I am replacing cell references with named ranges. The formula becomes:

=SUMPRODUCT(--(ColE=\$C\$21), ColD, 1/COUNTIFS(ColB, ColB, ColC, ColC, ColD, ColD, ColE, ColE))

Named Ranges
ColB -  \$B\$3:\$B\$18
ColC -  \$C\$3:\$C\$18
ColD -  \$D\$3:\$D\$18
ColE -  \$E\$3:\$E\$18

Step 1 - Filter records equal to condition in cell C21

=SUMPRODUCT(--(ColE=\$C\$21), ColD, 1/COUNTIFS(ColB, ColB, ColC, ColC, ColD, ColD, ColE, ColE))

--({1; 1; 1; 1; 1; 1; 1; 1; 2; 2; 2; 2; 2; 2; 2; 2}=1)

becomes

--({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})

Sumproduct can´t calculate TRUE/FALSE values. Let´s convert values to 1 and 0.

--({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})

becomes

{1; 1; 1; 1; 1; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 0}

Step 2 - Create array with values in col D

=SUMPRODUCT(--(ColE=\$C\$21), ColD, 1/COUNTIFS(ColB, ColB, ColC, ColC, ColD, ColD, ColE, ColE))

ColD

becomes

{5; 5; 100; 55; 47; 9; 9; 9; 4; 4; 100; 55; 47; 9; 9; 9}

Step 3 - Count duplicate records

=SUMPRODUCT(--(ColE=\$C\$21), ColD, 1/COUNTIFS(ColB, ColB, ColC, ColC, ColD, ColD, ColE, ColE))

1/COUNTIFS(ColB, ColB, ColC, ColC, ColD, ColD, ColE, ColE)

becomes

1/{2; 2; 1; 1; 1; 3; 3; 3; 2; 2; 1; 1; 1; 3; 3; 3}

and returns

{0,5; 0,5; 1; 1; 1; 0,333333333333333; 0,333333333333333; 0,333333333333333; 0,5; 0,5; 1; 1; 1; 0,333333333333333; 0,333333333333333; 0,333333333333333}

Step 4 - Return the sum of the products of the corresponding arrays

=SUMPRODUCT(--(ColE=\$C\$21), ColD, 1/COUNTIFS(ColB, ColB, ColC, ColC, ColD, ColD, ColE, ColE))

becomes

=SUMPRODUCT({1; 1; 1; 1; 1; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 0}, {5; 5; 100; 55; 47; 9; 9; 9; 4; 4; 100; 55; 47; 9; 9; 9}, {0,5; 0,5; 1; 1; 1; 0,333333333333333; 0,333333333333333; 0,333333333333333; 0,5; 0,5; 1; 1; 1; 0,333333333333333; 0,333333333333333; 0,333333333333333})

returns 216 in cell E21.