Summarizing Data Examples (an Overview)

Home page:
[View without Frames]
Summarizing Data Examples:  Auto Filter with Hotel Room Availability Example, Pivot Table, Sub Totals, Counts (COUNT, COUNTIF, SUM, SUMIF, SUMPRODUCT), Conditional Formatting, Sum of Visible Cells, array formulas (used with SUM).

Auto Filter     (#autofilter)

Conditional Format:   =MOD(SUBTOTAL(3,$A$1:$A1),3)=0
Shows color banding working with/without Filtering, but it does require that there be something in Column A for every row.  More on Color Banding on my Conditional Formatting page.

Filters, Validation, and Conditional Formatting are very similar to one another.  The purpose of the formula is test a condition and return either True or False, and act upon it if the condition is True.

The main purpose of this example is to show filtering by selecting only entries with "Black" as the color.

 A B C
1Prod.  Color  Price 
2AB0010Black 13.50
3AB0011White 13.75
4AB0012 Yellow 14.00
5AB0013Green 14.25
6AB0021Black 14.50
7AB0022 White 14.75
8AB0023Yellow 15.00
9AB0024Green 15.25
10AB0035 Black 15.50
11AB0036White 15.75
12AC0010Black 16.00
13AC0011 White 16.25
 A B C
1 Prod. Color  Price 
2AB0010 Black 13.50
6AB0021 Black 14.50
10 AB0035Black 15.50
12AC0010 Black 16.00
16AC0021 Black 14.00
20 AC0035Black 15.00
24AD0010 Black 15.50
28AD0021 Black 13.75
32 AD0035Black 14.75

The above table is a representation of the results of Data, Filter, Auto Filter

By clicking on the list button on Color the following drop down list appears.

(Top 10)

Suppose you wanted to show only the items where column C had number of parts to be ordered and you only wanted to show rows with values greater than zero to place an order.

Note the select columns as well as the drop down arrow in C1 have turned blue.  You can use the list as if the other value weren't there -- you can print, copy and paste.

To view all entries under a blued arrow once more, click on the blue arrow and choose "All".

To retain filters but show ALL data, use:  Data --> Filter --> Showall

To undo Auto Filter, simply use Data --> Filter --> (uncheck auto filter)

AutoFilter allows you to filter rows in or out depending on values in a column

With filtering, as with Conditional Formatting, it comes down to a formula or a condition that returns either TRUE or FALSE.

Auto Filter, Limitations

Article ID: Q105322;en-us;q105322 ...The article is short, so here is a copy... The information in this article applies to: -- Microsoft Excel for Windows, versions 5.0, 5.0c; Microsoft Excel for the Macintosh, versions 5.0, 5.0a; Microsoft Excel for Windows 95, version 7.0
SUMMARY -- When you use the AutoFilter command to filter data, the resulting drop-down list of unique items is limited to 251 identifiers. If the number of unique identifiers in a list is extremely large, you may receive "Not Enough Memory" errors or your computer may stop responding (hang). NOTE: The limit for Microsoft Excel version 7.0 is 250 due to the addition of one more built-in list item ("Top 10"). The limit in Excel 97 and Excel 98 has been increased to 1000.  If there are more than 1000 items to be selected one can add additional columns each with a different range.

Auto Filter, Custom

You can customize the AutoFilter.  There is a feature described by former Lotus 1-2-3 users that if they place "||" in first cell of a row, it will not be printed.  Excel is more flexible. 

Place "|" or any other character(s) into Column J in each row to be suppressed.  Select Cell J1, which should contain a description of the column then starting from the Data menu item select Filter.  Click on the blue arrow in cell J1.  You could choose blanks, but choose "custom" instead on the left side drop-down, choose "is not equal to".  On the right side drop-down choose "|".

Note the rows that are showing as a result of selection are in Blue.  If you select all cells (Ctrl+A) you will only select the visible cells and can paste those cells elsewhere.

Auto Filter choices:  equals, does not equal, is greater than, is greater than or equal to, is less than, is less than or equal to, begins with, does not begin with, ends with, does not end with, contains, does not contain.

Auto Filter, Custom, Unique

1 Name SeqCnt   =GetFormula(Cn)
2 bob  =COUNTIF(A$2:A2,A2)
3 sara  =COUNTIF(A$2:A3,A3)
4 bob  =COUNTIF(A$2:A4,A4)
5 sara  =COUNTIF(A$2:A5,A5)
6 dave  =COUNTIF(A$2:A6,A6)
7 john  =COUNTIF(A$2:A7,A7)
8 John  =COUNTIF(A$2:A8,A8)
9 Alex  =COUNTIF(A$2:A9,A9)
10 Alex  =COUNTIF(A$2:A10,A10)
11 george 10   =COUNTIF(A$2:A11,A11) 
1 Name
2  bob
3  sara
6  dave
7  john
9  Alex
11  george 
  Data --> Filter --> Advanced Filter

Filter this list in place
List Range:  $A:$A
Criteria Range:  (leave blank)
[X] Unique records only

Only based on Column A, all of
the data on the corresponding rows
would be selected

Hotel Rooms available on a Date, Example   (#hotelroom)

This example would be filtered on Column E, selecting TRUE, to show number of rooms available on the date shown in E1, you could use =Today() for the current date  The Buy and Sell dates, show purchase and sale of the entire hotel. 

1HotelRoomsBuy Sell01/15/198531062 
2Hotel 0132 01/01/1973  TRUE=AND(C2<=$E$1,OR(D2="",D2>=$E$1))  
3Hotel 0220 01/14/197501/15/2000 TRUE=AND(C3<=$E$1,OR(D3="",D3>=$E$1))  
4Hotel 0315 01/26/197701/14/1983 FALSE=AND(C4<=$E$1,OR(D4="",D4>=$E$1))  
5Hotel 04100 02/08/197901/14/1999 TRUE=AND(C5<=$E$1,OR(D5="",D5>=$E$1))  
6Hotel 0530 02/20/1981  TRUE=AND(C6<=$E$1,OR(D6="",D6>=$E$1))  
7Hotel 0620 03/05/198301/31/2000 TRUE=AND(C7<=$E$1,OR(D7="",D7>=$E$1))  
8Hotel 0737 03/17/1985  FALSE=AND(C8<=$E$1,OR(D8="",D8>=$E$1))  
9Hotel 0857 03/30/198712/31/1999 FALSE=AND(C9<=$E$1,OR(D9="",D9>=$E$1))  
10Room Tot.311   TRUE=1=1  
11   Formula used in cell B10: =SUBTOTAL(9,C2:OFFSET(C10,-1,0))  
See Related Area for additional AutoFilter references, including the Advanced Filter.

Time slices, counting Open problems (#timeslices)

Basically how many days was a problem open between 8:00AM and 8:59:59 AM, look under the 8 representing 8AM to just before 9AM.

Each row represents one problem, each entry under 0-23 represents problem days per problem within the indicated datetime range within a month.

 1    3    0   1  2  3  4   5  6  7  8   9  10  11  12   13  14  15  16   17  18  19  20   21  22  23 
 2 Date_Opened  Date_Closed in Mar in Mar                            
 3  2004-02-27 19:01 2004-03-01 08:30  2004-03-01 00:00 2004-03-01 08:30  -1  1  1  1   1  1  1  1  1  1   0  0  0  0  0  0   0  0  0  0  0  0   0  0  0 
 4  2004-03-01 08:00 2004-03-01 10:30  2004-03-01 08:00 2004-03-01 10:30  -1  0  0  0   0  0  0  0  0  1   1  1  0  0  0  0   0  0  0  0  0  0   0  0  0 
 5  2004-03-20 13:00 2004-04-05 13:59  2004-03-20 13:00 2004-03-31 23:59  10  11  11   11  11  11  11  11  11   11  11  11  11  12  12   12  12  12  12  12  12   12  12  12  12 
 6  2004-03-20 13:00 2004-03-28 13:59  2004-03-20 13:00 2004-03-28 13:59  7  8  8  8   8  8  8  8  8  8   8  8  8  9  9  8   8  8  8  8  8  8   8  8  8 
 7  2004-03-20 13:01 2004-03-28 13:59  2004-03-20 13:01 2004-03-28 13:59  7  8  8  8   8  8  8  8  8  8   8  8  8  8  9  8   8  8  8  8  8  8   8  8  8 
 8  2004-03-24 14:00 2004-03-25 13:00  2004-03-24 14:00 2004-03-25 13:00  0  1  1  1   1  1  1  1  1  1   1  1  1  1  0  1   1  1  1  1  1  1   1  1  1 
 9  Totals      29  29  29  29   29  29  29  29   30  29  29  28   30  30  29  29   29  29  29  29   29  29  29  29 
11  E3: =0+INT(D3)-INT(C3)-1        
12  F3: =0+((MOD($D3,1)>(F$1/24))+((MOD($C3,1)<(F$1+1)/24))+$E3)        
13  F3: =0+((MOD($D3,1)>(F$1/24))+((MOD($C3,1)<(F$1+1)/24))+$E3)        
14  H3: =0+((MOD($D3,1)>(H$1/24))+((MOD($C3,1)<(H$1+1)/24))+$E3)        
16  F6: =SUM(OFFSET(F$2,1,0):OFFSET(F6,-1,0))        
17  G6: =SUM(OFFSET(G$2,1,0):OFFSET(G6,-1,0))        

Pivot Table   (#pivot)

Sum of Price 
Color     listTotal
Green  86.00
Yellow  87.25
Grand Total440.50

Better example of an (onsite) Pivot Table «

  A Pivot Table is a useful tool for summarizing data, created using Data -> Pivot Tables, and then some drag and drop techniques.

Color associated with the product line example is selectable within the Pivot Table shown at left.  If additional fields were included, selecting black only might be more useful.

More information on Pivot tables by Harald Staff in his Introduction To Pivot Tables  If you have Excel 2000 you can copy and paste the sample data into your spreadsheet, and follow right along with the example.  A Pivot table does not update itself, so Harald has also included a macro that makes the tables update on selecting the worksheet:  (Harald wrote XL2GIF macro)   See Related Areas at bottom of this page for additional references.


Push down values in Column if not lowest in row (#pushdown)

Provide a means to reformat a table such that only the same value is on each row by pushing down values on row that are higher than the lowest non-blank value on a row.  This topic is Covered in Push down values in Column if not lowest in row

 1  2005-03-01  2005-03-02  2005-03-03
 2 apples apples  avocados
 3 bananas  bananas oranges
 4 oranges   peaches
 1  2005-03-01  2005-03-02  2005-03-03
 2 apples apples  
 3    avocados
 4 bananas  bananas 
 5 oranges   oranges
 6    peaches

Subtotals   (#subtotals)

 A B C
1PartQTY Color
2Part_#1 3Black
3Part_#1 1Yellow
4Part_#1 5Red
5Part_#2 4Black
6Part_#3  8Black
7Part_#3 2Red

Picture at the right was created by pasting into in MS PhotoDraw, cropped and saved (size 4K), grey, 85% compression, 324x156 bits, shown at 50%

John McGimpsey wrote a macro to create the subtotals, 2003-09-01, programming.  Header Row is required it subtotals on Column C (col 3), and creates totals of Column J (col 10) into Column N (col 3+11).

  Select Column A and Column B
Data --> subtotals -->
    Part / sum / (x) qty
    (x) Replace current subtotals
    (x) Summarize below total
then on spreadsheet view choose the [2] button to left of spreadsheet.
To return to normal Data --> [remove all] button lower left

data view


Elapsed Time Counting Techniques for Date Ranges:  COUNT, COUNTIF, SUM, SUMIF, counting

John Walkenbach has a fairly concise description and Examples for many of these functions. 
Suggest looking at these before continuing.
  COUNT, Tip 52: Cell Counting Techniques
  Tip 74:  Summing and Counting Using Multiple Criteria 
  Count and Sum Your Data in Excel, an article for Microsoft Office

Checking for Duplicates, also uses COUNTIF see Chip Pearson's
  Duplicate And Unique Items In Lists [Addition items in his Topic Index]

Excel Function Dictionary « by Peter Noneley is a reference to definitely download and maintain on your computer, the workbook has 157+ sheets each with an explanation and example of an Excel function.


1CheckedValue Formula in Column A comments
5 32   
76  =SUMIF(A2:A6,"x",B2:B6) simple formula
86  =SUMIF(A$2:OFFSET(A$8,-1,0),"x",B$2:OFFSET(C$7,-1,0)) simplifies insertion/deletion of rows
949  =SUMIF(A$2:OFFSET(A$9,-1,0),"<>x",B$2:OFFSET(C$7,-1,0))  
102  =MATCH("x",A$2:OFFSET(A$10,-1,0)) position in range
11x  =VLOOKUP("x",A$2:OFFSET(A$11,-1,0),1,FALSE) Found 1st column value
124  =VLOOKUP("x",A$2:OFFSET(C$12,-1,0),2,FALSE) found 2nd column value
133  =COUNTIF(A$2:OFFSET(A$13,-1,0),"<>x")  
142  =COUNTIF(A$2:OFFSET(A$14,-1,0),"x")  
Use of OFFSET is described in Why must we use that funny looking OFFSET Worksheet Function on my Insert Rows using a Macro webpage.


These can be useful techniques.  This was from a posting by Stephen on 7Jul2000 in misc.
There are two easy ways:


You could replace "Part#1" with a cell reference if you prefer.

The second construct is useful if you want to extend to more than one
Note multiplying the conditions will result in a 1 or a 0, you will not see this technique in Help for SUMRRODUCT.

 1 Instance Code  Color Value   
 2 1   D Blue 16    8  F2: =SUM(SUMIF(A:A,{"5","6","7"},D:D))
 3 2   E Yellow 36     
 4 3   C Red 42    178  F4: =SUM(SUMIF(B:B,{"B","C","D"},D:D))
 5 4   B Blue 73    178  E5: =16+42+73+1+8+38
 6 5   A Green 2     E6:
 7 6   H Blue 5    81  F7: =SUMIF(B:B,"B",D:D)
 8 7   C Red 1    81  F8: =SUMIF(B:B,"C",D:D)
 9 8   B Blue 8    16  F9: =SUMIF(B:B,"D",D:D)
10 9   C Green 38     E10:
11 10   E Brown 22    81  F11: =SUMPRODUCT((B2:B11="B")*(D2:D11))


1NAMEprogram1open closedprogram2openclosed
2JOEshopJan-2002 Jan-2002musicJan-2002Jan-2002
3FranshopFeb-2002  musicFeb-2002 
4J.R.shopMar-2002 Mar-2002musicMar-2002Mar-2002
5PaulshopApr-2002 Apr-2002musicApr-2002 
6MegshopApr-2002  musicMay-2002Mar-2002
7SarashopApr-2002  musicJun-2002Apr-2003

number of individuals with completed coursework both column D and G have completed entries
    2    =SUMPRODUCT((D2:D7<>"")*(G2:G7<>""))
number of individuals with incomplete coursework
    4    =ROWS(D2:D7)-SUMPRODUCT((D2:D7<>"")*(G2:G7<>""))

Extending formulas for more courses
as far as limits go there is no additional nesting, there is a specification limit of 1,024 characters in a formula.

Note the above formulas could have been entered as Array Formulas (Ctrl+Shift+Enter) using SUM instead of SUMPRODUCT.  The SUMPRODUCT formulas are a lot easier to work with.  SUMPRODUCT Function in more detail.  Some links for Array Formulas in the thread having this example.

Another SUMPRODUCT example

 ColumnA ColumnB ColumnC
 No 1  Apples
 No 2  Apples
 Yes 3  Apples
 Yes 4  Apples
 Yes 5  Apples
 No 1  Oranges
 Yes 2  Oranges
  @NA Oranges
 Yes 1  Pears
    Question Posted by a Stevec 2005-01-04
What formula could I copy for all cells in Column A so that will automatically show "No" "Yes" or a blank cell automatically.

Note that: No = the bottom half of a series of numbers Yes = the top half of a series of numbers (for odd series, there are always 1 more "yes" than "no" blank cell = for every cell in column b that = "@NA" "@NA" is not a formula generated error, it's "hard coded" into the cell

Solution provided by Roger Govier (2005-01-04)


Not everyone is taking every course, so the SUMPRODUCT formulas above would not be used here.


1NAMEStarted FiniAll DoneShop FiniMusicFini HistoryFini
2JOE3 2FALSEJan-02 Jan-02Jan-02Jan-02Jan-02 
3Fran3 3TRUEFeb-02 Jan-02Feb-02Jan-02Jan-02Jan-02
4J.R.0 0TRUE       
5Paul2 1FALSEApr-02 Apr-02Apr-02   
6Meg2 1FALSEApr-02    May-02Mar-02
7Sara2 0FALSE   Apr-02 Apr-02 
8Counts 127 FALSE4 34 24 2
10 Started at least one5        
11Started no courses 1       
12Finished no courses 2       
13Finished only one 2       
14 Finished one or more4        
15 Finished all courses1        
17B2 =COUNTA(E2,G2,I2)
18C2 =COUNTA(F2,H2,J2)
20B8 =SUM(B2:OFFSET(B8,-1,0))
21C8 =SUM(C2:OFFSET(C8,-1,0))
23E8 =COUNTA(E2:OFFSET(E8,-1,0))
24F8 =COUNTA(F2:OFFSET(F8,-1,0))
25D10 =COUNTIF(B2:B7,">0")
26D11 =COUNTIF(B2:B7,"=0")
27D12 =COUNTIF(C2:C7,0)
28D13 =COUNTIF(C2:C7,"=1")
29D14 =COUNTIF(C2:C7,">0")
30D15 =COUNTIF(D2:D7,"True")-COUNTIF(B2:B7,"=0")

Tables with True, False, and COUNTIF

1TitlesPrice CountM.TotalsAMY ANNBOBSAL
2Music.A 5.00 2 10.00 TRUEFALSETRUE FALSE
3Music.B 6.25 3 18.75 FALSETRUETRUE TRUE
5Music.D 7.00 2 14.00 TRUEFALSEFALSE TRUE
6   846.75      
7c2 =COUNTIF(E2:L2,"=True")     
8d2 =B2*C2     
10 Price CountM.TotalsAMY ANNBOBSAL
11Music.A 5.00 2 10.00 5.00 - 5.00 -
12Music.B 6.25 3 18.75 - 6.25 6.25 6.25
13Music.C 4.00 1 4.00 - 4.00 - -
14Music.D 7.00 2 14.00 7.00 - - 7.00
15   846.75 12.00 10.25 11.2513.25
16c11 =COUNTIF(E11:L11,">0")     
17c15 =SUM(C11:OFFSET(C15,-1,0))     
18d11 =B11*C11     
19d15 =SUM(D11:OFFSET(D15,-1,0))     
20e11 =IF(E2,$B2,0)     
21e15 =SUM(E11:OFFSET(E15,-1,0))     
The formulas shown are a result of GetFormula described on my Formula page.

COUNTIF and SUMIF formulas

1Name DeptSalary  Dept.TotalDept.Total
2John Doe 01 A40,000   =IF(COUNTIF($B$2:B2,B2)<>COUNTIF(B:B,B2),"",SUMIF($B$2:$B$93,B2,$C$2:$C$93))
3John Doe 02A 60,000  (use fill-handle to replicate formula downward)
4John Doe 03A 70,000170,000  
5John Doe 07B 60,000  
6John Doe 08B 40,000  
7John Doe 11B 40,000140,000  
8John Doe 12C 60,000  
9John Doe 13C 60,000  
10John Doe 14C 40,000  
11John Doe 15C 70,000230,000  
12Total    540,000 =SUM(D$2:OFFSET(D12,-1,0))

Conditional Formatting   (#condfmt)

The key to using Conditional Formatting (C.F.) is to reduce a formula so that it indicates TRUE or FALSE (1 or 0).  C.F. overrides normal cell formatting.  You are limited to the use of three conditional formattings per worksheet (like three wishes).  C.F. began with XL97.  For XL95 you would have to write your own Event macros.

Some specific examples, as they appear in newsgroups:

These examples tell what to select before invoking Format --> Conditional Formatting and all are Formula is, and after coding your formula, choose your format (pattern and font color), the first you see without selecting a tab is the FONT.  When choosing an interior color you might also choose the borders because gridlines disappear with interior color and the borders chosen will only exist while C.F. is in effect.

To get smallest half (rounded up) of the entries in bold, change the formatting formula to

Consecutive Duplicates:  To compare value in column A with value below, but not include the difference being blank or empty.  Select ALL cells then enter the following conditional formatting formula: 

Duplicated Anywhere in Column:  Need not be sorted

Conditional Formatting Formulas are considerably harder to show and find once used, unless you know which range to pick.  Each Conditional Format has a range that you can't just see, anyway the following is a start and will work best if it just has a formula, rather than "is less than" type of conditions.

Function CondFormula(myCell, Optional cond As Long = 1) As String
  'Bernie Deitrick programming 2000-02-18, modified D.McR 2001-08-07
  CondFormula = myCell.FormatConditions(cond).Formula1
End Function
More information on Conditional Formatting

Sum of Visible Cells in a Range   (#visiblesum)

The SUBTOTAL Worksheet Function will provide various totals or counts if the cells were hidden with a filter. The following will check for hidden rows and hidden columns to exclude them form the total.  The use of Volatile must be added and recalculatioin will only occur when Calculation happens (i.e.  F9).
Function VisibleSum(cellrange As Range) As Double
   Application.Volatile   'posted 2000-12-03, D.McRitchie, mod. 2002-02-12
   Dim cell As Range
   For Each cell In cellrange.cells
     If Not (cell.Rows.Hidden Or cell.Columns.Hidden) Then
                   If IsNumeric(Cell.Value) Then
         VisibleSum = VisibleSum + cell.Value
                         end if
     End If
End Function
The use of volatile in any functions used on any sheet can really slow things down.  Depending on your data, and what it is that you are hiding, you might be able to use SUMIF Worksheet Function, or SubTotals on the Data menu.  (see Summarizing Data, and Auto Filter (an Overview)

Clear repeated group   (#groups)

1StCity -FM
1StCity -FM
8 DouglasKRMC
10 NogalesKNOG
11 YumaKYRM
14  KRCD
15 OxnardKXRS
16  KXLM
You want to write a macro that starts from the bottom and checks the cell above from left to right.
Option Explicit
Sub Clear_groups()
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Dim r As Long, lrow As Long
  lrow = Cells.SpecialCells(xlLastCell).Row
  For r = lrow To 2 Step -1
   If Cells(r - 1, 1) = Cells(r, 1) Then
     Cells(r, 1) = ""
     If Cells(r - 1, 2) = Cells(r, 2) Then
       Cells(r, 2) = ""
     End If
   End If
  Next r
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Related Areas

This page was introduced on July 4, 2000. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2007,  F. David McRitchie,  All Rights Reserved