SumIf Formula

[Kim2]

Hi, I am working on a workflow, and I would like to be able to do a sumif formula. I have read to use a summarize tool but it does not seem to be working for what I need to do. What I am trying to do is predict the amount of $ that we will be retiring in future years. Example : Minor Category Useful Life Rule FY23 FY24 FY25 COMP HARDWARE 3 YRS 3 3 2016 2017 2018 COMP HARDWARE 5 YRS 5 2 2015 2016 2017 Data: Minor Category Year Placed in Service Total COMP HARDWARE 3 YRS 2016 1,549,419.48 COMP HARDWARE 3 YRS 2017 2,263,136.31 COMP HARDWARE 3 YRS 2018 1,115,817.05 COMP HARDWARE 3 YRS 2019 2,367,232.60 COMP HARDWARE 3 YRS 2020 804,957.08 COMP HARDWARE 3 YRS 2021 118,137.88 COMP HARDWARE 5 YRS 2015 431,429.37 COMP HARDWARE 5 YRS 2017 113,130.01 COMP HARDWARE 5 YRS 2018 503,108.79 COMP HARDWARE 5 YRS 2019 330,292.21 COMP HARDWARE 5 YRS 2020 327,019.30 COMP HARDWARE 5 YRS 2021 320,178.22 What I am looking for my output to be: Minor Category FY23 FY24 FY25 Comp 3 Year 1,549,419 2,263,136 1,115,817 Comp 5 Year 431,429 - 113,130 Each minor category has different years so I am trying to do a sumif to pull in the total amount based off of the rule that is associated with the category by year. Any guidance on this would be greatly appreciated! Thank you! Kim

[cplewis90]

In order to accomplish the same as a SUM IF, you would need to complete each of the portions of a SUM IF in Alteryx. I would recommend using a filter tool before the summarize tool to accomplish that. In the filter tool you could create a custom filter and write an IF statement that would say 'if [category] = "1st option" then "1st option rule"' you would finish this statement for each rule you are looking to execute. Once you do that connect a summarize tool to the True output of the filter tool and then you could sum what is needed. This should create the same functionality as a SUM IF in excel.

[DawnDuong]

hi @Kim2 

Are you able to show a mock-up excel of the desired outputs from a mock-up input?

Cannot follow the question 100%.
Dawn.

[Kim2]

Hi Dawn, For some reason it will not allow me to browse or drag a file in this reply.

[DawnDuong]

hi Kim,

I am guessing... did you try to drag a file into the "Post reply" window? That does not work, unfortunately. At least for myself, initially i did not know how to attach a file....

In case you are in my previous shoe (and please pardon me if i'm saying something that you already know): You need to click on the Reply button (see below) and then click on "browse" to attached.

[Kim2]

Hi,

Thank you! It seemed I actually just needed to go into a different web browser.

Please see attached!

Thank you,

Kim

[DawnDuong]

hi @Kim2 

You are trying to run the accounting depreciation expenses for different categories of fixed assets right? Just to confirm that I understand the nature of the requests right.

the reason I asked is because there are 2 columns in tab "Rules" that can be used to calculate the depreciation. Which column "Useful life" or "Rule" should be use to determine the depreciation period?
Also, some rules don't make sense. E.g. INTERNAL DEV 5 YEARS have "Useful life" = 5 and "Rule" = 10.

If you can clarify I may be able to find sometime to help you (no promise though...)

Dawn.

[Kim2]

Hi,

So basically I am trying to calculate our future asset retirements. So on the rules tab under FY23, FY24 & FY25 those are the years that the assets are tagged to will need to be retired. So for example looking at Computer HW 3 YR in FY23 we want to see what assets were tagged to Computer HW 3 YR in 2016 and those assets would be our estimated retirements in FY23. So looking at the data tab in row 36 we can see computer HW 3 YR in 2016 was $1,549,419.48 which is what I would need to be returned on my output tab under Comp HW 3 YR under FY23. 

Thanks,

Kim

[DawnDuong]

hi @Kim2 

So basically columns B and C are irrelevant.

Take Comp Hardware 3 Yrs. The rule is that if it was purchased in 2016 then it should be retired in FY23, if purchased in 2017 then should be retired in FY24. 
Is that right? 

[Kim2]

Hi,

Yes that is correct!

Thanks,

Kim