Multi-layered CountIFS in Excel

Question

I've read the other posts on this but none seem to cover my challenge or level of incompetence.

I dont know enough about COUTIFS in excel to fully understand what they do but I have been asked to convert a XLS to Alteryx.

The data looks like this but its column O I'm struggling with.

Any help would be better than me saying I cant do it!

Convert XLS IFS To Alteryx.xlsx

clmc9601 · Answer

Hi @GuyJHarvey, just wanted to follow up-- did this solution work for you? Thanks.

clmc9601 · Answer

Hi @GuyJHarvey,

I built a workflow that might help you!

I also translated the countif from your spreadsheet's excel syntax for short term transfer into English if that helps:

IF result > 1 of both the following
- Part 1 Consider the item number from the row below:

* Then count the rows with that item number that are active items and have over stock on hand

- Part 2 Consider the item number from the row below:

* Then count the rows with that item number that are active items and have on-hand shortage

It struck me as a little strange to keep referencing the row below (even when a completely different item number), but I'm sure there's a good reason for it.

Convert IFS to Alteryx.yxzp

Lisa_M · Answer

A COUNTIF in Excel is a conditional count - so show how many records satisfy specified conditions.

In theory, it should be pretty simple to replicate in a workflow - I think I'd use a formula tool and conditional statements to add markers where the Excel conditions are detailed and satisfied, then a summarise tool and a count on the marker field added.

Hope this helps.