## Sum unique distinct invoices

*Article updated on November 28, 2017*

### 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:

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:

**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

Identify duplicate invoice records using conditional formatting

In a previous article Identify duplicate invoice records in excel I created a list of the original invoices and their […]Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article