Alteryx Substitute for Sum Product =SUMPRODUCT(--(A1:A6=225))

[ashaw7]

Hey Everyone,

Looking for a Alteryx substitute for the below formula:

=SUMPRODUCT(--(A1:A6=225))

I have a unique order number and a Delivery Creation Date - I create a unique xref using this which results in this format (7207362363144187) on my Delivery Checks

I have a data spreadsheet in which data is extracted from into my Delivery Checks.

I'm trying to create the same xref format of (7207362363144187) but keeps formatting as (72073623632020-05-22) 

I then need to use =SUMPRODUCT(--(A1:A6=225)) to bring back how many times this unique ref comes back on the spreadsheet.

Countif doesn't recognise the 44187 date part as a unique value so that is why I use the above formula.

But I'm trying to automate my work but I cant seem to figure out a tool or method to produce the same output.

I need the spreadsheet exactly how it is just another column added at the end with the correct count in.

Appreciate your support

thanks 

[JagdeeshN]

Hi @ashaw7 ,

Please find below certain community discussion around the same function:-

1. https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Sumproduct-Excel-formula-in-Alteryx/td...

2. https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Sumproduct/td-p/635286

3. https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Simulate-sumproduct-Excel-formula-in-A...

Please do let me know if any of these solutions apply to your usecase.

Best,

Jagdeesh

[GavriloPrincip]

Says these 3 were all archived. How do I access that archived content as I need the solution as well?

[apathetichell]

Hey @GavriloPrincip - after you come back from the assassination check out my solution which I posted on one of the threads.

1) create a field of the logs of your numbers. (you can use any base for your log - but just use the natural log, or remember your base)

2) sum the logs.

3) use your base (or e if you took natural logs)^sumlogs (use the pow function) to create your new value which is your sumproduct.

[GavriloPrincip]

That was a long time ago so no need to "cancel me" over it lol

I'm new to Alteryx, so couldn't really follow your steps that well (sorry), but I will try to play with the tools and learn. 

Thank you!

[apathetichell]

111 years in June - but it seems like just yesterday!

All good - so quick steps:

1) formula tool. - create a new fixed decimal column based upon the numbers you want to sum product (use LOG([yourfield])

see -> https://help.alteryx.com/current/en/designer/functions/math-functions.html#example-6846016-12

2) use the summarize tool - group by whatever you need to group by - sum your new new field from 1

3) use the formula tool -> new field - EXP([sum_your field from 2])

resulting value from 3 is your sumproduct.