This seems confusing at first but if you keep your data and reporting in separate sheets your spreadsheet will be flexible and can grow in size, adding more information. ![]() =SUMPRODUCT((PartNo Range Data ws = PartNo Report ws)*(WeekNo Range Data ws = WeekNo Report ws)*(Qty In Range) or (Qty Out Range)) The Qty Out and Qty In columns formula would be: With this concept your Current Stock formula will be =SUMIFS(Your Entire Qty In Range in data worksheet, Your Entire Part Number Range in data worksheet, Your Part Number Cell in report worksheet, Your Entire Week Column in data worksheet, "<=" & Your Week Number Cell) - SUMIFS(Your Entire Qty Out Range in data worksheet and the same logic for part number and week but ). All you have to do is use the same symbol for each operationaddition is represented by a + sign, subtraction by a - sign, and division by a / sign. On the report sheet you enter under in the part number column, your part number and under the week number the week for which you want to see the results. How Do You Add, Subtract, and Divide in One Formula in Excel Adding, subtracting, and dividing in one formula in Excel is easier than you might think. On the report sheet you could have Part Number, Week Number, Current Stock and even Qty In, Qty Out if you wish. You could create a worksheet with the following columns : Part Number, Week Number, Qty In, Qty Out. ![]() It's better to keep one separate worksheet for data and another for reporting. Re: Add & Subtract alternating columns from each other.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |