Doing Sumifs using absolute referencing range which is dynamic like we have in Excel.

[Gk9090]

Hello everyone,

I have been trying to replicate the sumifs function in column F from the attached excel sheet in Alteryx. I would have simply used the Summarize Tool but the sumif function has the following 2 complexities to it-

1) The Sumifs function is using absolute referencing range. Please refer the screen shot below - The Sumifs is using  the following logic-

 Sum column E  where column C range (C3 :C54 for first row), "End of Period", is smaller than equal to the "End of Period Date" of the current row AND where column range C3:C54 is greater than equal to the "Start of Period" of the current row. 

2) Second complexity is that I have to sum column E (for the above mentioned conditions) only from current row to next 52 rows and not the entire data range. 

Can anyone please help me replicate the functionality of this Sumif function in Alteryx? I have attached the file for reference. I need to replicate the Sumifs formula in column F.  

Thanks in advance.

[Qiu · Accepted solution]

@Gk9090 
Thank you to you, and I refreshed my Excel functions. 😁

I think it is a typical Batch Macro application.
We will take each row of data than take 51 more rows data to do a append then perform the SUMIF function in the Filter tool.

[Gk9090]

Thank you so very much Qiu for the prompt response. I am new to Alteryx and therefore taking a while to digest your solution but this seems just what I wanted. 

Thanks again for your help. :) 

[Qiu]

@Gk9090 
Glad be to be helpful and welcome to the world of alteryx.
You will find it very fancinating.

You can always come here for questions and many here would love to extend their help. 😁

[aanderson99]

@Qiu you seem very well versed in SUMIFs and un-tangling them for Alteryx, I'm hoping you can help me with the below conversions from Excel to Alteryx:

This formula is for our duplicate identifier (which identifies if one item has a "matching offset")

And this formula is simply for the text direction of "Match" or "Partial Match" - I'd like to get both of these functions into Alteryx for mass, aggregated data comparisons:

Thanks in advance to any and all who are able to assist. 

[Qiu]

@aanderson99 
I am not really good at Excel functions...😁
I can take a try but can you provide a sample data for both functions? so I can verfiy them in Alteryx.