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.