Multiple Sumif in Alteryx

Hello, I am looking to create a table that displays the transportation volume for each warehouse under all possible scenarios that 1 or more warehouses are down. In Excel, I've used the SUMIFS function, but I have a large volume of data. I’ve tried running total tool and summary tool, but not working.

I have data for various warehouses and the items they transport to the store. The column “First/ Second/ Third” indicates the priority of each warehouse for product transportation. For instance, if Warehouse A is unavailable (i.e. Down), Warehouse C will be used to transport "Apple". If Warehouse A and C are Down, then Warehouse B will transport “Apple”.

My goal is to create a table that reflects the volume each warehouse would need to transport if other warehouses are unable to deliver goods due to bad weather. For example, if both Warehouse A and Warehouse C are out of operation simultaneously, I would like to know the transportation volume required from the remaining warehouses.

Sample Data I have:

Items	First	Second	Third	Volume
Apple	Warehouse A	Warehouse C	Warehouse B	100
Orange	Warehouse C	Warehouse A	Warehouse C	150
Pineapple	Warehouse B	Warehouse A	Warehouse D	200

I've included an updated sample table that I would like to achieve below:

If there are only 3 Warehouses, the possible combinations to consider are as follows. “XXX” means the total volume that the warehouse will have to transport:

Warehouse	Case 1	Case 2	Case 3	Case 4	Case 5	Case 6
Warehouse A	Down	xxx	xxx	Down	xxx	Down
Warehouse B	xxx	Down	xxx	Down	Down	xxx
Warehouse C	xxx	xxx	Down	xxx	Down	Down

Initially, I thought of using Excel's SUMIF function to achieve this, but with so many combinations to consider for over 10 warehouses, it's becoming a time-consuming task. I was wondering if Alteryx might have a more efficient approach to help me achieve this.

Appreciate in advance!

Macros

Iterative Macro

Accepted answers

ntakeda

Hi @Vic_S

I have created a workflow. Please check the attached file.

I designed it as follows—does this meet the requirements?

The inputs are two tables:

1.A table with case and Down combinations

2. A priority table each items

The output consists of case and the total volume that the warehouse will have to transport.

I created two patterns:

Pattern 1: The output does not include the word "Down."

Pattern 2: The output includes the word "Down."

If there are any discrepancies or if you have any questions, please let me know.

multiple_sumif.yxmd

All comments

AshRez

If I understood your requirement correctly, You are trying to get for each Warehouse, the max volume if all other warehouse failed, is that right?
it wasn't clear what you meant with Case 1, 2, and 3.

but I would think you need something that convert Columns "First", "Second", "Third" into rows to be able to sum the total volume per warehouse.

It should be something like these screenshots.

Multiple Sumif in Alteryx1.PNG

Multiple Sumif in Alteryx2.PNG

Multiple Sumif in Alteryx3.PNG

Multiple Sumif in Alteryx4.PNG

Vic_S

Hello, apologize for any confusion. I'm aiming to create a table that visualizes the maximum volume for each warehouse in scenarios where one or more warehouses are unavailable. I have a large dataset covering over 10 warehouses, and I'd like to analyze various cases.

To break this down further, the table should display different scenarios. For instance, the table should illustrate the outcomes when Warehouse A is down, when both Warehouse A and B are down, and so forth

I've included an updated sample table that I would like to achieve below:

If there are only 3 Warehouses, the possible combinations to consider are as follows. “XXX” means the total volume that the warehouse will have to transport:

Warehouse	Case 1	Case 2	Case 3	Case 4	Case 5	Case 6
Warehouse A	Down	xxx	xxx	Down	xxx	Down
Warehouse B	xxx	Down	xxx	Down	Down	xxx
Warehouse C	xxx	xxx	Down	xxx	Down	Down