Get Shift Done: Tips and Tricks
The IF statement surely is among the most commonly-used formulas in Google Sheets. It’s used for looking up data or making calculations under specific conditions. But did you know that the IF statement can help you keep your sheets looking clean and more readable?
That can be important, or at least a time-saver. When setting up a spreadsheet into which you import, add, or copy and paste data frequently, it can be a pain to drag or copy formulas down to your new data.
For example: We created a spreadsheet in which to track inventory for our gift shop. We have a tab for when we purchase inventory, and another for when we sell inventory. Instead of looking up the inventory number, we use a SUMIF statement that looks for the inventory name matching the item we sold, and then fills in the inventory number for that item.
We don’t want to have to remember to write out the formula, or even copy or drag it down to our new “items sold” for each addition. The usual practice is to copy the formula for each line and leave it to update when a new item is entered. Only, now we see a column with cells that calculate to 0 because there is nothing in the cell we want to look up.
Here’s how to make our spreadsheet look clean without all those zeros, without updating the formula every time we enter in new inventory that has been sold.
By using the IF Statement, we tell the cell to show up blank when the inventory item is blank, and to show the SUMIF formula when it isn’t.
It can help to say to yourself, “If C2 is blank then blank, otherwise SUMIF.”
Then just drag your formula down, or copy and paste for each cell in that column. The cells in the column will be blank on the page; but when you add new “sold” inventory the cells populate with the inventory number.
GSD: Tips and Tricks is brought to you by Xero, the cloud accounting software solution for your small business. With Xero, you can log in anytime, anywhere to get a real-time view of your cash flow and manage your books. Start your free 30-day trial today.