Microsoft Office Tutorials and References
In Depth Information
Working with Dates and Times
Formula
Description
{=SUM((Month=”Jan”)*(Region=”North”)*Sales)}
Sum of Sales in which Month=”Jan” and
Region=”North”
=SUMIFS(Sales,Month,”Jan”,Region,”North”)
Sum of Sales in which Month=”Jan” and
Region=”North” (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Region=”North”)*Sales)}
An array formula that returns the sum of Sales
in which Month=”Jan” and Region=”North”
=SUMIFS(Sales,Month,”Jan”,Region,”<>North”)
Sum of Sales in which Month=”Jan” and Region
<> “North” (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Region<>”North”)*Sales)}
An array formula that returns the sum of Sales
in which Month=”Jan” and Region <> “North”
=SUMIFS(Sales,Month,”Jan”,Sales,”>=200”)
Sum of Sales in which Month=”Jan” and
Sales>=200 (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Sales>=200)*(Sales))}
An array formula that returns the sum of Sales
in which Month=”Jan” and Sales>=200
=SUMIFS(Sales,Sales,”>=300”,Sales,”<=400”)
Sum of Sales between 300 and 400 (Excel
2007 and later)
{=SUM((Sales>=300)*(Sales<=400)*(Sales))}
An array formula that returns the sum of Sales
between 300 and 400
Other counting tools
Other ways to count or sum cells that meet certain criteria are:
h Filtering (using a table)
h Advanced filtering
h The DCOUNT and DSUM functions
h Pivot tables
For more information, consult the Help system.
Working with Dates and Times
Excel uses a serial number system to store dates. The earliest date that Excel can understand is
January 1, 1900. This date has a serial number of 1. January 2, 1900, has a serial number of 2, and
so on.
Most of the time, you don’t have to be concerned with Excel’s serial number date system. You
simply enter a date in a familiar date format, and Excel takes care of the details behind the
scenes. For example, if you need to enter August 15, 2010, you can simply enter the date by
typing August 15, 2010 (or use any of a number of different date formats). Excel interprets your
entry and stores the value 40405, which is the serial number for that date.
 
Search JabSto ::




Custom Search