Hi Fellow Excel Addict,

Thanks for joining me for another edition of my time-saving, mind-opening Excel tips. I want to wish all my fellow Canadian subscribers and their families a Happy Canada Day on July 1st and all my American subscribers a great 4th of July.

Now that summer is in full swing up here in Canada and the kids are out of school I'm looking forward to having an awesome summer. In Canada we usually get only two or three good months of summer so we really do take advantage of it when it finally does arrive.

My summer usually includes a pretty full schedule of soccer. My two girls are playing soccer and I am coaching the girls all- star team. I'll be taking my holidays in a little over a week, so I'm really looking forward to that.

I hope you and your loved ones are having a safe and enjoyable summer (or winter, depending which half of the globe you're on).

Once again, I encourage you to send me feedback on my tips (see P.S. note below).

Until next week ...

Keep on Excelling,
Francis J Hayes (The Excel Addict)

P.S. VERY IMPORTANT Please remember to include "TheExcelAddict" in the Subject line of any email you send me. This will help it stand out in the flood of junk email I have been receiving lately.

DISCOVER NEWFOUNDLAND AND LABRADOR, CANADA:
This is where I grew up: http://www.brigus.net/
This is where I now live: http://www.conceptionbaysouth.ca/
This is my beautiful province: http://www.gov.nf.ca/tourism/

If you have several columns hidden (i.e. columns G:K) and you want to unhide just one (i.e. column H), here's a trick that will avoid you having to unhide all the columns first and rehide the others.

In the name box (the small white box to the left of the formula bar) type a cell address that is in the column you want to unhide. In this example, H1. Cell H1 will be selected even though the column is hidden. Now, from the Format menu select Column, Unhide. Just column H is unhidden.

The same trick will work for unhiding a hidden row.

Jim Rohn's Weekly E-Zine is a free weekly publication dedicated to providing valuable insights and information that can help empower and improve both your business and personal life. Find out more at http://www.TheExcelAddict.com/rd/jimrohn.htm.

All you have to do to qualify each month is send an email to your Excel friends and associates recommending my newsletter and website. Include a BCC to francis@TheExcelAddict.com and for each person that you refer, your name will go into the draw. Please, recommend only to people you know and who are Microsoft Excel users.

All you have to do each month is click here to recommend my newsletter and website to a friend. For each person that you refer, your name will go into the draw. The more Excel friends you refer, the more chances you have of winning, but please, recommend it only to people you know and who are Microsoft Excel users.

If you use the 'Tell A Friend' link at www.TheExcelAddict.com you could win $10,000 and you will also qualify for my Referral Contest draw.

*** Each monthly winner will be notified by email and will have 10 days to reply with a shipping address. If I don't receive a reply within 10 days, another name will be drawn.

Referral Contest Winners
March February January December November October September August July June	Mike Lowry, Cairnes, Australia Ashley Rankine, Perth, Australia Luis Bolivar, Maracaibo, Venezuela Joseph Mathew, Dubai, United Arab Emirates Alexandre Gusmao, Lisbon, Portugal Apollo Tigalo, Angeles City, Philippines Jim Henderson, California, USA Beki Chambers, Indiana, USA Keith Bannister, North Carolina, USA Lisa Hobbs, Pennsylvania, USA

If you have been using Excel for any length of time, I'm sure you have had a need to sum data in a list that meets a specific condition. For example, let's say you use a spreadsheet to record your expenses, and you categorize your expenses such as Food, Entertainment, Utilities, etc.. Let's assume that your table contains the following information: column A = Date, column B = Cheque #, column C = Category, column D = Amount, and column E = Details.

Somewhere outside your table you want to track how much you are spending in each category.

The solution you are looking for can be found with Excel's SUMIF function.

The structure or syntax of SUMIF is = SUMIF(RangeToEvaluate,Criteria,RangeToSum)

When you use SUMIF, you are telling Excel to look in the RangeToEvaluate for data that matches your Criteria, and for each cell that meets the Criteria, add up the corresponding cells in the RangeToSum and put the result in this cell.

In our example table, to calculate the total of all your Entertainment expenses, somewhere outside your table you could use the formula =SUMIF(C1:C50,"Entertainment",D1:D50). For each cell in range C1:C50 (the Category column) that contains the word Entertainment, the function would add the corresponding cell in range D1:D50 (the Amount column). The total of your SUMIF formula would be your total Entertainment expenses.

Also, instead of typing the Criteria right into the function, you could use a reference to a cell that contains your criteria so you could easily change your criteria without touching the formula. For example =SUMIF(C1:C50,G2,D1:D50)

If you will be copying this formula to other cells, you will need to make your RangeToEvaluate and RangeToSum cell references absolute. That is, they will not adjust when they are copied. In the Formula Bar, highlight the C1:C50 and press the F4 key until there is a dollar sign in front of both the row and column references (i.e. $C$1:$C$50). Repeat for D1:D50. Now your formula should look like =SUMIF($C$1:$C$50,G2,$D$1:$D$50)

There are tens of millions of other Microsoft Excel users all over the world. The vast majority of them have a very limited knowledge of Excel's capabilities. My goal is to share this information with everyone I possibly can and help them to benefit from Excel's amazing capabilities.

Please help me spread the word about my newsletter. If you know other Excel users, please tell them about "Spreadsheet Tips From An Excel Addict". I'm sure they'll appreciate it.

Maybe you know someone who supervises or employs many Excel users. That's where your referral could have a big impact.

VERY IMPORTANT: Please refer only people you know personally, who use Microsoft Excel and would benefit from this newsletter.

A while ago I showed you how you could open a file from Windows Explorer in a program that it wasn't associated with by using File, Open With... I'll fill you in on that tip a bit later.

When you double-click on a text file in Windows Explorer, does it automatically open in Microsoft Word, or some other bulky word processing program? There may be times when you would prefer to open a text file in a smaller, simpler program like NotePad or WordPad.

There is another option to open the file in a different program without changing file associations.

1. Make sure the program you want the file to open in is open. In this example we'll use NotePad.
2. Locate the text file in Windows Explorer.
3. Left-click the file and drag it to the NotePad button on the Taskbar at the bottom of your screen. Don't drop it yet!
4. Hold the cursor over the button for a second or two and the NotePad application window will pop up. Don't drop it yet!
5. Drag the file up to the middle of the NotePad window and NOW you can drop it.
6. The text file will open in NotePad.

If you find that you are always using a certain program to open a certain file type this way, you may want to change the file association for that file type so that you can simply double- click in Windows Explorer to open it. To do this, left click the file, hold down the Shift key while clicking File on the menu, click Open With..., select the program, and select the 'Always use this program...' option. Now every time you double-click a file of that type, it will automatically open in that program.