asked on

Sumproduct identical values only once

Hi,

Im trying to Sumproduct a product that appears more then once with the same criteria. If I use the formula to get the value of the product "x" "=SUMPRODUCT(--(product=x),value)" it will return x= 420

What im looking for is to sum only once and to get 100+50+35 meaning 185.

Group Product Value
A x 100
A x 100
A y 25
A y 25
B x 50
B x 50
B x 50
B y 10
C x 35
C x 35

Thank you very much for your support!

Ryan Chong

try use Array Formula (press Ctrl + Shift + Enter) with:

=SUM(IF(B1:B11 ="x", IF(IF(MATCH(C1:C11, C1:C11, 0)=(ROW(C1:C11)-ROW(C1)+1), (ROW(C1:C11)-ROW(C1)+1), 0)<>0, C1:C11, "")))

Open in new window

Ryan Chong

or try use Array Formula (press Ctrl + Shift + Enter) with:

=SUM(IF(FREQUENCY(IF(B1:B11="x",MATCH(C1:C11,C1:C11,0)),ROW(C1:C11)-ROW(C1)+1),C1:C11))

Open in new window

Marius Ioana

ASKER

Thank you,

The formula works to some extent,

If the table is like this is will not work anymore. Could you help me with some solution.

The formula used is: =SUM(IF(col_b =E1, IF(IF(MATCH(col_c, col_c, 0)=(ROW(col_c)-ROW($C$1)+1), (ROW(col_c)-ROW($C$1)+1), 0)<>0, col_c, "")))

But the result is incorrect

a      x      100            x      100
a      x      100            y      25
a      y      25            z      60
a      y      25
a      z      50
a      z      50
b      x      50
b      x      50
b      z      10
b      x      50
b      y      10
c      x      25
c      x      25
c      z      100

Thank you

Ryan Chong

any reason your data looks like this in Excel?

can you make them to be in a proper sorting instead?

Marius Ioana

ASKER

To make it more clear:

603305      9/03/2016      200      1209010270                  200
603305      9/03/2016      200      1209010270
603305      9/03/2016      200      1209010270
603314      25/07/2016      200      1209010270
603314      25/07/2016      200      1209010270
603314      25/07/2016      200      1209010270

The result should be 1209010270 = 400 pcs

the formula used as array:

=SUM(IF(FREQUENCY(IF(D1:D6=D1,MATCH(C1:C6,C1:C6,0)),ROW(C1:C6)-ROW(C1)+1),C1:C6))

Thank you very much

Marius Ioana

ASKER

Tried to sort them by x,y,z but there is no difference.

I believe the more relevant is my latest example. Its simple but i can not make it work.

Marius Ioana

ASKER

I think the formula should involve also the first column.

ASKER CERTIFIED SOLUTION