Sales order data is reported. Only dimensions in the report are product and sales volume. Products sold are A, B, C or D.

Functions like sum, average, max and min should be calculated per product. Eg. How many pieces of product A was sold, how much was the highest single sales volume for product B, how much was the average sales volume for product C and so on.

For generating data for products A, B, C or D, we need CHAR function. Function CHAR returns a digit eg. CHAR(65) is A (upper case a), and CHAR(97) a (lower case a).

=CHAR(RANDBETWEEN(65;68))

Excel randomly selects 65, 66, 67 or 68. That means A, B, C or D.

For sales volumes any value between 1 and 10.

=RANDBETWEEN(1;10)

That is the sales volume data.

You can create sales data manually but using RANDBETWEEN function reduces manual work.

Sum for product can be counted with SUMIF and SUM functions.

For SUMIF function, you need to define where product range is found, in our case cells are C4:C27. As the idea is to copy the formula, make this reference as absolute reference. Next you need to define product, which is search in product range, that is in G9. The product should change when the formula is copied further, therefore this reference is relative reference. Last, the sum range needs defining, the sum range can be found in cells D4:C27 where is the sales data. The reference should be absolute because even though the formula is copied, the sum range is the same.

With SUMIFS function you can count with several criterias. In this example only one criteria, product, is used. It anyway returns the same value as SUMIF.

Using SUM function is somewhat more complex, this is my opinion. The formula you need to write to the cell H9 is:

=SUM(IF($C$4:$C$27=G9;$D$4:$D$27)) and press SHIFT + CONTROL + ENTER the result is:

{=SUM(IF($C$4:$C$27=G9;$D$4:$D$27))}

SUM function needs nested IF formula, inside IF, you need define product range C4:C27 as absolute reference equals to wanted product value in G9. Last you need to define sum column meaning the sales data. That is in cells D4:D27. Mark sales data with absolute reference as it should not be changed when the formula is copied downwards.

AVERAGE formula works in the same way as SUM function. You just replace SUM by average like

=AVERAGE(IF($C$4:$C$27=G9;$D$4:$D$27))

Don’t forget SHIFT + CONTROL + ENTER.

You can change AVERAGE to some other formula too. If you want to know standard deviation per product sales data, you can use STDEV.S or STDEV.P instead of AVERAGE.