Similar function of countif and averageif in alteryx

[123love55448]

For example, if I have 2 input files. First file containing customer name. Another file containing customer name, quantity, total price, & status. I want to know to use something like countif and average if in this case, desired output as below.

Input File 1:

Customer name

AA

BB

Input File 2:

Customer name         Quantity            Price             Status

AA                                 2                       5                Order completed

AA                                 5                      50               Order completed

BB                                 1                       5                Order completed

BB                                 5                       80              Add to cart

Desired output:

Customer name	Avg. Quantity in Cart	Avg. Price in Cart	Avg. Quantity of Order completed	Avg. Price of order completed
AA	0	0	3.5	27.5
BB	5	80	1	5

Very appreciated to all helps

[OllieClarke]

@123love55448 
How's this:

If you want other methods of aggregation (e.g. count) then these can be ticked as well in the cross-tab tool

Hope that helps,

Ollie

[Qiu]

@123love55448 
Just a bit different approach.

[Emmanuel_G]

Hi @123love55448 ,

Find attached a very easy way to do this.