SUMPRODUCT with SIGN (Conditional Sum without duplicates)

[collegeitdept]

Hello,

Does any one know of the proper sumproduct function that's a conditional sum (lookup) without including the duplicates please? I believe it includes the SIGN function within:



[TABLE="class: grid, width: 485"]
<tbody>[TR]
[TD]Product Group[/TD]
[TD]Level[/TD]
[TD]Model[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]Boundless[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Boundless[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Hideaway[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Hideaway[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Hideaway[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Hideaway[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Knife Set[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Knife Set[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Knife Set[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Knife Set[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Knife Set[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Card Players[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Card Players[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Card Players[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Card Players[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Card Players[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


Answer Grid:

[TABLE="class: grid, width: 346"]
<tbody>[TR]
[TD]Product Group[/TD]
[TD]Level[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]Boundless[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD]Hideaway[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Knife Set[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Card Players[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brewery[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In the RESULT column, is where the formula should be to provide the sum total of units by Product Group & Level.

The function should use the sumproduct to lookup the PRODUCT GROUP against the LEVEL. Then sum the count numbers for ONLY the UNIQUE model numbers for each product group levels.

EX: Brewery 1: Result should be 21 (8+6+7) because for level 1 there 3 unique models (101=8, 102=6, 103=7)

Thanks

 

[Marcelo Branco]

Maybe

Assuming your data in A1:D43


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Product Group​

[/TD]
[TD]

Level​

[/TD]
[TD]

RESULT​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Boundless​

[/TD]
[TD]

1​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Brewery​

[/TD]
[TD]

1​

[/TD]
[TD]

21​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Hideaway​

[/TD]
[TD]

1​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Knife Set​

[/TD]
[TD]

1​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

Card Players​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

Brewery​

[/TD]
[TD]

2​

[/TD]
[TD]

27​

[/TD]
[/TR]
</TBODY>[/TABLE]


Array formula in H2 copied down
=SUM(IF(FREQUENCY(IF($A$2:$A$43=F2,IF($B$2:$B$43=G2,MATCH($A$2:$A$43&$C$2:$C$43,$A$2:$A$43&$C$2:$C$43,0))),ROW($A$2:$A$43)-ROW($A$2)+1),$D$2:$D$43))

confirmed with Ctrl+Shift+Enter

Another possible formula...
=SUMPRODUCT(--($A$2:$A$43=F2),--($B$2:$B$43=G2),--(MATCH($A$2:$A$43&$C$2:$C$43,$A$2:$A$43&$C$2:$C$43,0)=ROW($C$2:$C$43)-ROW($C$2)+1),$D$2:$D$43)

confirmed with just Enter

I *think* the first formula is a tad faster

Hope this helps

M.

 

[collegeitdept]

Maybe

Assuming your data in A1:D43


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Product Group​

[/TD]
[TD]

Level​

[/TD]
[TD]

RESULT​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Boundless​

[/TD]
[TD]

1​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Brewery​

[/TD]
[TD]

1​

[/TD]
[TD]

21​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Hideaway​

[/TD]
[TD]

1​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Knife Set​

[/TD]
[TD]

1​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

Card Players​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

Brewery​

[/TD]
[TD]

2​

[/TD]
[TD]

27​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in H2 copied down
=SUM(IF(FREQUENCY(IF($A$2:$A$43=F2,IF($B$2:$B$43=G2,MATCH($A$2:$A$43&$C$2:$C$43,$A$2:$A$43&$C$2:$C$43,0))),ROW($A$2:$A$43)-ROW($A$2)+1),$D$2:$D$43))

confirmed with Ctrl+Shift+Enter

Another possible formula...
=SUMPRODUCT(--($A$2:$A$43=F2),--($B$2:$B$43=G2),--(MATCH($A$2:$A$43&$C$2:$C$43,$A$2:$A$43&$C$2:$C$43,0)=ROW($C$2:$C$43)-ROW($C$2)+1),$D$2:$D$43)

confirmed with just Enter

I *think* the first formula is a tad faster

Hope this helps

M.

Why wouldn't the SIGN function apply?


I thought that's its purpose in SUMPRODUCT - to only sum/count the unique conditions?

Thanks

 

[barry houdini]

I thought that's its purpose in SUMPRODUCT - to only sum/count the unique conditions?

No, SIGN function doesn't have any specific application in this type of calculation. SIGN, if applied to a single value, just returns 1 if that number is positive, -1 if negative and 0 if the number is zero.....but the usage that you are probably thinking of in SUMPRODUCT simply converts an array of TRUE/FALSE values to 1/0 values, so in Marcelo's suggested SUMPRODUCT formula you get the same result with

--($A$2:$A$43=F2)

as with

SIGN($A$2:$A$43=F2)

 

[collegeitdept]

Thanks!