Many of my clients keep a “running total” of sales. They put “Dates” in Column “A” and enter each day’s sales in Column “B.” Next is Column “C” which contains the formula =SUM(B$:B250). That is fine – it works.
However, if you use the =SUMIF() function to total Year-to-Date (YTD) Sales you gain flexibility, save space and save time – because you do not have to scroll down several screens to see your YTD Sales total!
In this video, I show you how to use “Named Ranges” in a =SUMIF() Formula to keep track of your cumulative sales total.
Here are the steps to follow in this lesson:
- Traditionally, you use a formula like =SUM(B$3:B250) to give you a “running total” for YTD Sales.
- The=SUMIF() Function returns the SUM of vales that meet a “Single Criterion” (the IF portion) .
- The 1st Argument is – What is the Range that you want your criteria to evaluate? Consider “Naming” this Range – it makes it easier to write your formula and explain your formula.
- The 2nd Argument is – What is your criteria? Be sure to use this syntax – “Comparison Operator”
- In the video example we also need to include the “&” to join the cell “D2.” Cell “D2,” in this example, contains the Function =TODAY()
- The 3rd Argument is – What Range do you want to SUM to return the values that meet your Criteria?
- The =DATE() Function returns the serial number for a specific date. It requires 3 Arguments. =Date, Year, Month, Day)
- You will save time and gain productivity when you “Name Cells and Ranges” and then you them in your formulas.
- Use the F3 shortcut key to “Paste Names” into your formulas.
News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.
Find the Excel Video Lesson that you want – Index to all Excel Topics
Link to this post!