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 ?

Answer:

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.

Download excel sample file for this tutorial.

Sum unique distinct invoices.xlsx
(Excel 2007/2010 Workbook *.xlsx

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria

Related posts:

Count unique distinct records in excel 2007

Fetch data from another table

Count unique distinct values that meet multiple criteria in excel

Count unique distinct records with a date and column criteria in excel 2007

Filter unique distinct records with a condition in excel 2007