# Thread: Sumproduct Query Excel Thanks: 0 Likes: 0

1. ## Sumproduct Query Excel

Hi,

I'm trying to replicate a sumproduct formula in Power query editor within a custom column.

Previously used working formula:
=IF(SUMPRODUCT((\$C\$2:\$C2=C2)*(\$E\$2:\$E2=E2))>1,0,1)

I know sumproduct isn't used in power query, and after searching the net have attempted to use SumX but this doesn't seem to be recognised either.

Currently I have :
if [#"W/Order"]*[Part Number]=[Part Number]>1 then 0 else 1

My data example, F is the custom column:
 A B C D E F Work Order1 Op 1 Part Number1 Work Order1 Op 2 Part Number1 Work Order1 Op 3 Part Number1 Work Order2 Op 1 Part Number2 Work Order2 Op 2 Part Number2 Work Order3 Op 1 Part Number1 Work Order3 Op 2 Part Number1

Can anyone help or suggest a better way to achieve my goal.

The goal is to generate a pivot table where I can filter by a part number with an occurrence of more than 3.  Reply With Quote

2. ## Re: Sumproduct Query Excel Originally Posted by Excel_Blonde Hi,

I'm trying to replicate a sumproduct formula in Power query editor within a custom column.

Previously used working formula:
=IF(SUMPRODUCT((\$C\$2:\$C2=C2)*(\$E\$2:\$E2=E2))>1,0,1)

I know sumproduct isn't used in power query, and after searching the net have attempted to use SumX but this doesn't seem to be recognised either.

Currently I have :
if [#"W/Order"]*[Part Number]=[Part Number]>1 then 0 else 1

My data example, F is the custom column:
 A B C D E F Work Order1 Op 1 Part Number1 Work Order1 Op 2 Part Number1 Work Order1 Op 3 Part Number1 Work Order2 Op 1 Part Number2 Work Order2 Op 2 Part Number2 Work Order3 Op 1 Part Number1 Work Order3 Op 2 Part Number1

Can anyone help or suggest a better way to achieve my goal.

The goal is to generate a pivot table where I can filter by a part number with an occurrence of more than 3.

I found the below on another site but couldn't get it to work. After further searching I thought it was due to the ',' so I changed it to then and else but still not working. It still displays Token RightParen expected. Any ideas?

=if (
CALCULATE (
COUNTROWS ( Test1 ),
FILTER (
ALLEXCEPT ( Test1, Test1[Works Order Number] ),
Test1[Index] <= EARLIER ( Test1[Index] )
)
)
> 1,
0,
1
)

Current:
=if (
CALCULATE (
COUNTROWS ( Test1 ),
FILTER (
ALLEXCEPT ( Test1, Test1[Works Order Number] ),
Test1[Index] <= EARLIER ( Test1[Index] )
)
)
> 1 then
0 else
1
)

Any help greatly appreciated.  Reply With Quote

## User Tag List

number1, order1, part, query, work 