SUMIFS

Excel  2007 and Excel /2010 offers some new functions that go beyond the simple SumIf and CountIF as they allow you to test up to 127 conditions. The new functions are Sumifs, CountIfs and AverageIfs. (Excel 2003 does not offer AverageIf but Excel 2007 does). They are called the Plurals due to the S at the end of the function.

They all work the same way and the syntax is similar. We are going to add up the Sales for Pam's Tea Parlor if the Country of Origin is China  and the Quantity purchased is greater than or equal to 3.   Only 2 rows meet all the criteria and I have highlighted them in purple so they were easy for you  to find.

Here is the formula: =SUMIFS(G7:G21,A7:A21,A7,C7:C21,"China",F7:F21,">=3")

Let's walk through the steps:
SYNTAX
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Sum_range
The sum range is G7.G21
This is the range of cells that you want to do the math on. In our case we want to sum up the Sales column.

Criteria_range1 is A7.A21
This is  first range is the criteria that everything else is based off of. In our example, we are filtering our data  based upon  Column A where the customers are.

Criteria1  is "Pam's Tea Parlor" or A7. 
This is the criteria that Excel is going to look for in the Criteria_Range1 which in our case is Column A.
I used A7 instead of typing "Pam's Tea Parlor" since it saves me worrying about typos and I was not copying this formula so I didn't need to worry about relative and absolute cell references. If you do type in text then you need quotation marks.

So far, we have told Excel to sum the sales if the Customer is Pam's Tea Parlor.
The criteria1  selection  must match something in Criteria_range1  and the criteria2 selection must match something in the Criteria_range2 selection etc.  All further conditions step off Criteria1.

The remaining sytnax is optional and you can continue adding up to 127 criteria. We are only going to add two more- Origin of China and Quantity greater than or equal to 3.

Criteria_range2 is C7.C21
We area filtering our data further and limiting our interest to teas from specific locations that have been ordered by Pam's Tea Parlor

Criteria2 is "China"
Criteria2 must match an item in the column that was specified in Criteria_range 2. We are only looking for teas from China that were ordered by Pam's Tea Parlor. Again, text needs to be in quotations.

Criteria_range3 is F7.F21
This is telling Excel that our next interest is Column F - Quantity

Criteria3 is >=3
Quantity greater than or equal to 3. If you do not put quotations around >=3, Excel will generally add them in even though it seems incorrect. The syntax on SUMIF and SUMIFS is not the same as an IF function.

However please be aware that I could only test for one criteria per column unless you can use a wild card to meet your criteria.

In other words,  I can only test for one country of origin unless I can use a wild card. Excel would not provide an answer if  I had wanted to sum sales for Pam's Tea Shop if the tea came from China and Korea and the quantity was greater than or equal to 3.  I could only select a single country of origin.

AND OR Functions

AND OR ?????

Many people are not familiar with the AND or the  OR  logic  functions. Even people who are familiar with them tend to associate them as part of a nested IF statement ; however, you can use them as stand alone formulas.

AND and OR logic functions allow you to test up to 30 conditions.

The syntax for these two functions is:
Function(logical1, logical2.......)

For an AND function to be true ALL conditions must be met whereas with an OR only ONE  of the conditions must be met.

Below is a simple example:

I have a customer aging and I want to test it.
In Row 2, I have asked Excel to tell me if the City is Indianapolis AND the State is IN AND the Business is Bicycles OR the Balance is greater than 1000 to display True.

Column G shows the resulting answer and Column H shows the calculation.
 All 3 of the conditions within the AND syntax must be correct for the answer to be true OR the amount needs to be greater than 1000.
The formula at row 2 is =OR(AND(B2="Indianapolis",C2= "IN",D2="Bicycles"),E2>1000)

The same formula was used in Rows 3-5 just the information in Columns B through F were changed.

Inside the AND, Excel is testing for 3 conditions. The AND is nested inside of an OR statement.
Row 2 displays True as all 3 of the AND conditions are met. The Balance condition is not met.
Remember for an OR to display as true only one of the conditions needs to be met and the OR had two conditions - the Balance Amount and the AND conditions.

At Row 3, the answer is False because one of the AND conditions was not met. The city displays as  Muncie- not Indianapolis and the Balance test was not met.
At Row 4, the answer is False because Column D displays Parts and not Bicycles so all of the AND conditions were not met and the OR test was not met as the balance is not greater than 1000.
At Row 5, the answer displays as True because the balance is greater than 1000 even though none of the AND conditions are met.

By themselves the resulting answer of a AND and OR are only True and False which can be useful in itself as seen above however you can  you nest them within an IF and create an even more powerful calculation.

For example, I could take the exact same formula and nest it inside an IF and have it display Current if the conditions are met (True)  or Overdue if the conditions are NOT met (False).

=IF(OR(AND(B2="Indianapolis",C2="IN",D2="Bicycles"),E2>1000),"Current","Overdue")


There is also a NOT logic function however that only tests one condition.

If you want to learn more about Decision Making functions, check out our Decision making Excel course.It offers 3 hours of CPE.  Click here.

Apps for 2013: Business, Travel, Fun

Happy Holidays!

I hope that you all had a great holiday season. We were quite busy. First to Boston for Christmas with my family and then to Smugglers Notch in Vermont. We tried snow shoeing for the first time which was a blast. It was the best skiing in quite a few years although driving to the Manchester airport in the blizzard conditions was not fun.

As you may have figured out, we have become an Apple family - even my husband! Last year I shared with you some recommended apps and I thought I would add some more that might be of interest if you do in fact have an Iphone, IPad or Ipad Mini. All the apps listed below are free although some may have upgraded versions that cost money. This is just my personal list of apps. If you have any you want to share please do- I love to check out new apps.

The Kindle is still my favorite app. Unfortunately Apple's Book app while pretty visually is worthless as its book offering is minimal. Once you set up the Kindle App, you can order E books from Amazon and they automatically transfer and update on your Ipad. It is so easy.

Business Apps- there are a few that might be of interest to you.
Dropbox, and GoToMy PC are still two of my favorites from last year.
DocuSignInk is an app that my husband uses quite a bit. It allows you to sign your documents anytime, anywhere. It can be quite handy.
OneNote- I am always jotting notes on small pieces of paper and my desk overflows with them and of course you can never find the note you need. One of my last years's New Years resolutions was to get more organized so I downloaded OneNote. This is a nice app because One Note is part of Office 2010 so you can synchronize between your PC and your phone. It is a very nice program - I just need to remember to use it more.

Cam Scan is an app that I have just downloaded and it looks good. To be honest, it is usually just easier to take a picture of a page - essentially what the scanner apps do although this app apparently will convert it into a PDF and allow you to annotate it.

Adobe Reader can be useful if you are trying to read PDFs. I have had mixed results. I have not been able to get it to work with the CPE E-books that I sell on www.cpaselfstudy.com. I have  figured out a work around though. I have found that if you email yourself the PDF that the iPad and iPhone will open it from within the mail message just fine.. go figure!

QR Reader - my daughter and husband use this one all the time. Just hold your iPhone over the bar code in a store or on a product and it will read it.

Best Utility Apps
I am not sure that Find iPhone is actually a utility but it needs to be on your phone somewhere. You can set it up so that if you lose your iPhone,iPad, Ipad Mini or  Mac etc it will locate it for you. You can also set it to delete all data on the iPhone or other Apple product as well.

Flashlight can really come in handy.

Best Navigation utilitiesThank goodness Google Maps now has an app although I have become attached to MapQuest since it is good at pointing out if you have overshot or missed a turn.
If you travel, a number of cities such as Boston have mobile taxis that will pick you up very quickly -usually in a nice upscale car. You can pre-arrange or call as needed. The only one I have personally used is Uber in Boston (Uber is also now  in other cities) and they were very quick and professional.  New York is in the process of approving e-hailing  taxis so you will be seeing a lot more of these apps. It's one of those apps you want installed for when you really need it.

Health Apps 
Walk Tracker is the best. It uses GPS to track how many miles you have gone and how many calories you have burned. It also integrates with Itunes so you can listen to music as you walk or jog. The only downside is it is a huge battery drain.  If your News Years Resolution entails calorie watching check out MyFitness Pal. It works on the PC as well as the I phone. It is the best one I have found and I have looked at quite a few.

Travel Apps
TripAdvisor is my absolute favorite although Yelp and UrbanSpoon come in handy particularly if you are in a major city.
OpenTable allows you to make reservations on the fly.
I always try to fly Southwest and their app is great for Check In and finding out about delays.
SeatGuru will tell you the best and worst airline seats by flight#- this is really the most useful on international flights. isn't it amazing that there is an app for this!

Games
There are so many and to be honest my daughter introduces me to a lot of them.
Bejeweled - my absolute favorite
Spider Solitaire - 2nd favorite

Fun Logic games: 

• Flow Free 
• Fling
• Iassociate2- this is such a cool game....
• Sudoku2
• LineUp2

Fun Word Games

• Iassociate2 fits into both logic and word game 
• SpellTower - my husband is now addicted to this one
• Moxie
• WhirlyWord
• Words for Friends

I just downloaded a bunch of new ones over the holidays - can't wait to try them out.


Best wishes to you and your family over this holiday season.
I hope 2013 is a great year for you.

No Grandma!

I saw this photo today and it cracked me up - how many of you can identify with it?

Excel Arrays - Published by Texas CPA Society

We were really excited that the Texas CPA Society published our article "Incorporating Excel Arrays into Your Audit Plan". Click here to access a copy the Array article.

If it is of interest we have a more detailed version of that course for which you can earn CPE credit -
Using Arrays to become a Power Excel user.

We offer a lot of different Excel CPE courses. so if you are a CPA check them out when you get a chance.


Here is a link to other articles that we have written over the last few years.



Best wishes to you all and your families. I hope that you have a wonderful Thanksgiving.