Forum Discussion
Challenge 3
Okay, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
When you successfully confirm a Formula as an Array formula by confirming it with keystrokes Ctrl+Shift+Enter, you will find that the formula in the formula bar gets surrounded by the curly brackets.
In I8
=SUM(IFERROR((--(FREQUENCY(IF($E$8:$E$27=E8,MATCH($D$8:$D$27&$E$8:$E$27,$D$8:$D$27&$E$8:$E$27,0)),ROW($D$8:$D$27)-ROW($D$8)+1)>0))*($G$8:$G$27),0))-SUMIF($E$8:$E$27,E8,$F$8:$F$27)Don't forget to confirm this with Ctrl+Shift+Enter and then copy it down.
In the first description in J8 which says this...
For A : Purchase(200+250)-sum(Selling Quantity)=160
Where does this part (200+250) comes from? What is 200 and what is 250 here and how do you calculate this?
If you calculate this from a range of cells, please mention the source range for this calculation.
Also does code has any role in this calculation of only Items are considered in this calculation?
Where does this part (200+250) comes from? What is 200 and what is 250 here and how do you calculate this? Its a Purchase Quantity, Two time we have had Purchase which is repeated in this column, (the purchase quantity repeated till the Sum selling quantity be equal to purchase quantity for Each Purchase Code for each item)
200: purchase quantity for Code 102 and 250 Purchase quantity for Cod 202 for item A in total we have 450 Pc for item AOkay, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
When you successfully confirm a Formula as an Array formula by confirming it with keystrokes Ctrl+Shift+Enter, you will find that the formula in the formula bar gets surrounded by the curly brackets.
In I8
=SUM(IFERROR((--(FREQUENCY(IF($E$8:$E$27=E8,MATCH($D$8:$D$27&$E$8:$E$27,$D$8:$D$27&$E$8:$E$27,0)),ROW($D$8:$D$27)-ROW($D$8)+1)>0))*($G$8:$G$27),0))-SUMIF($E$8:$E$27,E8,$F$8:$F$27)Don't forget to confirm this with Ctrl+Shift+Enter and then copy it down.
