Excel formule

[Alwin23]

 I have a question about excel. I have a list of old orders with the purchase price and the number of products sold that are all put in different categories. Now I want to use a formula to ensure that I can calculate the total purchase price by multiplying the purchase price per line with the number of products sold. But now I want to be able to calculate this by category. I first came up with the following (wrong) formula: = SUMSIF(Y:Y; N:N; "Christmas inside tree")*SUMSIF(E:E; N:N; "Christmas inside tree") . purchase price:(Y:Y), number of products sold:(E:E), category column:(N:N), category: "Christmas inside tree". This formula did calculate the total purchase price, but did not document this per line, so it was incorrect. Then I add the following formula: = SUMS.IF('Raw data'!Y:Y; ' Raw data'!N:N; "Christmas inside tree") * SUMPRODUCT('Raw data'!E:E; ' Raw data'!N:N; "Christmas inside tree"). This formula was also incorrect according to excel and indicated: #VALUE. Do you know what the correct formula could be?

[#2 · Accepted solution]

@Alwin23 

= SUMPRODUCT(($N$2:$N$20="Christmas inside tree")*$E$2:$E$20*$Y2:$Y20)

Does this return the intended result?

= SUMPRODUCT((N2="Christmas inside tree")*E2*Y2)

This formula calculates the result in every row. The formula is in cell W2 and filled down in the example.