Effortlessly Bill for Excel for Product Quality Management

Bill for excel for Product quality

welcome to XL magic trick number 348 hey if you want to download this workbook and follow along click on my YouTube channel and click on my college website link and you can download the workbook XL magist trick 344 to 348 hey uh this is a little mini version of records for invoices so we sold a m the particular Mouse the price was 20 we sold 20 of them the total was uh that amount now this is uh a database with records for invoices and on a separate sheet you could imagine you could have the stock this is the running total of inventory units on hand this is the total now I put these things onto the same sheet just to make the video easy but is there a formula we could use that would always look at our invoice and I made it real short you know this database could have you know thousands of rows is there a formula we could put right here that every time we add a new record it will calculate the running total sure there is how about this equals whatever the total is minus and we need some way of looking through this whole column finding all of the mouse ones then jumping over to this adding all of those up and subtracting them with the perfect function Su if the range now there's one two three arguments and I've done lots of videos on some if but this range right here holds the criteria this is a specific criteria obviously for the inventory it'll be Mouse and then Su range that's going to be the inventory that we want to subtract so range this one right here all the way down imagine if it was a th000 rows or whatever and then hit F4 comma what's the criteria relative cell reference right there and then comma the sum range now most people just go ahead and highlight the whole thing but by default some if is programmed to take what whatever the number of cells here that is exactly the sum if determines how many to add up from this argument not this argument so all you need is the top one and I'm going to hit F4 cuz that's got to be locked close parentheses control enter double click and send it down sure enough it got uh 120 minus 20 this one got 1,200 minus 12 for the total now let's go ahead and test it Mouse I better uh type it correctly and then I'm just going to put the quantity and sure enough that updates let's do one more camera you obviously want to have data validation here based on that to make sure and then I'll uh sell one so now 1,999 all right uh there you have it uh we'll see you next trick