Search code examples
excelexcel-formulasumproductunique-values

Find the SUMPRODUCT of all unique values of a specific column?


The math is simple. First match up all the units and their respective unit cost per invoice number. Multiply each unit by the unit cost and then find the SUMPRODUCT of each unique invoice number. Here is what I have so far done.

Dataset with the SUMPRODUCT formula

I can find the SUMPRODUCT of each unique invoice number by using the formula =SUMPRODUCT(-- (A$2:A$136 = value), C$2:C$136* D$2:D$136). However, I have to manually insert the value into each cell. Is there a formula that can look at each invoice number on every row and then insert that value into the SUMPRODUCT formula? The answers should look like this.

The answers I want to see in the total column


Solution

  • Change

    A$2:A$136 = 473394
    

    to

    A$2:A$136 = A2
    

    enter image description here