Summarizing Data Examples (an Overview)

Location: http://www.mvps.org/dmcritchie/excel/sumdata.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [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

1 Prod. Color Price

2 AB0010 Black 13.50

3 AB0011 White 13.75

4 AB0012 Yellow 14.00

5 AB0013 Green 14.25

6 AB0021 Black 14.50

7 AB0022 White 14.75

8 AB0023 Yellow 15.00

9 AB0024 Green 15.25

10 AB0035 Black 15.50

11 AB0036 White 15.75

12 AC0010 Black 16.00

13 AC0011 White 16.25

A B C

1 Prod. Color Price

2 AB0010 Black 13.50

6 AB0021 Black 14.50

10 AB0035 Black 15.50

12 AC0010 Black 16.00

16 AC0021 Black 14.00

20 AC0035 Black 15.00

24 AD0010 Black 15.50

28 AD0021 Black 13.75

32 AD0035 Black 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.

All
(Top 10)
(custom)
Black
Green
White
Yellow

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.

Select row 1 or cell C1
Data --> Filter --> Auto Filter
On the drop down in cell C1 choose (Custom)
on Left side: Greater than
on Right side: 0
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 http://support.microsoft.com/default.aspx?scid=kb;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

A B C D

1 Name Seq Cnt =GetFormula(Cn)

2 bob 1 1 =COUNTIF(A$2:A2,A2)

3 sara 2 1 =COUNTIF(A$2:A3,A3)

4 bob 3 2 =COUNTIF(A$2:A4,A4)

5 sara 4 2 =COUNTIF(A$2:A5,A5)

6 dave 5 1 =COUNTIF(A$2:A6,A6)

7 john 6 1 =COUNTIF(A$2:A7,A7)

8 John 7 2 =COUNTIF(A$2:A8,A8)

9 Alex 8 1 =COUNTIF(A$2:A9,A9)

10 Alex 9 2 =COUNTIF(A$2:A10,A10)

11 george 10 1 =COUNTIF(A$2:A11,A11)

A

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.

A B C D E F G

1 Hotel Rooms Buy Sell 01/15/1985 31062

2 Hotel 01 32 01/01/1973 TRUE =AND(C2<=$E$1,OR(D2="",D2>=$E$1))

3 Hotel 02 20 01/14/1975 01/15/2000 TRUE =AND(C3<=$E$1,OR(D3="",D3>=$E$1))

4 Hotel 03 15 01/26/1977 01/14/1983 FALSE =AND(C4<=$E$1,OR(D4="",D4>=$E$1))

5 Hotel 04 100 02/08/1979 01/14/1999 TRUE =AND(C5<=$E$1,OR(D5="",D5>=$E$1))

6 Hotel 05 30 02/20/1981 TRUE =AND(C6<=$E$1,OR(D6="",D6>=$E$1))

7 Hotel 06 20 03/05/1983 01/31/2000 TRUE =AND(C7<=$E$1,OR(D7="",D7>=$E$1))

8 Hotel 07 37 03/17/1985 FALSE =AND(C8<=$E$1,OR(D8="",D8>=$E$1))

9 Hotel 08 57 03/30/1987 12/31/1999 FALSE =AND(C9<=$E$1,OR(D9="",D9>=$E$1))

10 Room 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.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
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
10
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)
15
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 Total

Black 132.50

Green 86.00

White 134.75

Yellow 87.25

Grand Total 440.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

A B C

1 2005-03-01 2005-03-02 2005-03-03

2 apples apples avocados

3 bananas bananas oranges

4 oranges peaches

A B C

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

1 Part QTY Color

2 Part_#1 3 Black

3 Part_#1 1 Yellow

4 Part_#1 5 Red

5 Part_#2 4 Black

6 Part_#3 8 Black

7 Part_#3 2 Red

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

COUNT, COUNTIF, SUM, SUMIF (#count)

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.

SUMIF, COUNTIF, COUNT

A B C D

1 Checked Value Formula in Column A comments

2 w 16

3 x 4

4 y 1

5 32

6 x 2

7 6 =SUMIF(A2:A6,"x",B2:B6) simple formula

8 6 =SUMIF(A$2:OFFSET(A$8,-1,0),"x",B$2:OFFSET(C$7,-1,0)) simplifies insertion/deletion of rows

9 49 =SUMIF(A$2:OFFSET(A$9,-1,0),"<>x",B$2:OFFSET(C$7,-1,0))

10 2 =MATCH("x",A$2:OFFSET(A$10,-1,0)) position in range

11 x =VLOOKUP("x",A$2:OFFSET(A$11,-1,0),1,FALSE) Found 1st column value

12 4 =VLOOKUP("x",A$2:OFFSET(C$12,-1,0),2,FALSE) found 2nd column value

13 3 =COUNTIF(A$2:OFFSET(A$13,-1,0),"<>x")

14 2 =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.

SUMIF, SUMPRODUCT (#sumif)

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

=SUMIF(A1:A6,"Part#1",B1:B6)
=SUMPRODUCT((A1:A6="Part#1")*(B1:B6))

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
condition:
=SUMPRODUCT((condition1)*(condition2)........*(number_range))
Note multiplying the conditions will result in a 1 or a 0, you will not see this technique in Help for SUMRRODUCT.

A B C D E F G

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))

SUMPRODUCT Example

A B C D E F G

1 NAME program1 open closed program2 open closed

2 JOE shop Jan-2002 Jan-2002 music Jan-2002 Jan-2002

3 Fran shop Feb-2002 music Feb-2002

4 J.R. shop Mar-2002 Mar-2002 music Mar-2002 Mar-2002

5 Paul shop Apr-2002 Apr-2002 music Apr-2002

6 Meg shop Apr-2002 music May-2002 Mar-2002

7 Sara shop Apr-2002 music Jun-2002 Apr-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
=ROWS(D2:D7)-SUMPRODUCT((D2:D7<>"")*(G2:G7<>"")*(I2:I7<>"")*(K2:K7<>"")*(M2:M7<>""))
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)
A2:
=IF(NOT(ISNUMBER(B2)),"",IF(B2<SUMPRODUCT(--($C$2:$C$2000=C2),
--ISNUMBER($B$2:$B$2000),$B$2:$B$2000)/SUMPRODUCT(--($C$2:$C$2000=C2),
--ISNUMBER($B$2:$B$2000)),"No","Yes"))

COUNTA, COUNTIF Example

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

A B C D E F G H I J

1 NAME Started Fini All Done Shop Fini Music Fini History Fini

2 JOE 3 2 FALSE Jan-02 Jan-02 Jan-02 Jan-02 Jan-02

3 Fran 3 3 TRUE Feb-02 Jan-02 Feb-02 Jan-02 Jan-02 Jan-02

4 J.R. 0 0 TRUE

5 Paul 2 1 FALSE Apr-02 Apr-02 Apr-02

6 Meg 2 1 FALSE Apr-02 May-02 Mar-02

7 Sara 2 0 FALSE Apr-02 Apr-02

8 Counts 12 7 FALSE 4 3 4 2 4 2

9

10 Started at least one 5

11 Started no courses 1

12 Finished no courses 2

13 Finished only one 2

14 Finished one or more 4

15 Finished all courses 1

16

17 B2 =COUNTA(E2,G2,I2)

18 C2 =COUNTA(F2,H2,J2)

19 D2 =B2=C2

20 B8 =SUM(B2:OFFSET(B8,-1,0))

21 C8 =SUM(C2:OFFSET(C8,-1,0))

22 D8 =B8=C8

23 E8 =COUNTA(E2:OFFSET(E8,-1,0))

24 F8 =COUNTA(F2:OFFSET(F8,-1,0))

25 D10 =COUNTIF(B2:B7,">0")

26 D11 =COUNTIF(B2:B7,"=0")

27 D12 =COUNTIF(C2:C7,0)

28 D13 =COUNTIF(C2:C7,"=1")

29 D14 =COUNTIF(C2:C7,">0")

30 D15 =COUNTIF(D2:D7,"True")-COUNTIF(B2:B7,"=0")

Tables with True, False, and COUNTIF

A B C D E F G H

1 Titles Price Count M.Totals AMY ANN BOB SAL

2 Music.A 5.00 2 10.00 TRUE FALSE TRUE FALSE

3 Music.B 6.25 3 18.75 FALSE TRUE TRUE TRUE

4 Music.C 4.00 1 4.00 FALSE TRUE FALSE FALSE

5 Music.D 7.00 2 14.00 TRUE FALSE FALSE TRUE

6 8 46.75

7 c2 =COUNTIF(E2:L2,"=True")

8 d2 =B2*C2

9

10 Price Count M.Totals AMY ANN BOB SAL

11 Music.A 5.00 2 10.00 5.00 - 5.00 -

12 Music.B 6.25 3 18.75 - 6.25 6.25 6.25

13 Music.C 4.00 1 4.00 - 4.00 - -

14 Music.D 7.00 2 14.00 7.00 - - 7.00

15 8 46.75 12.00 10.25 11.25 13.25

16 c11 =COUNTIF(E11:L11,">0")

17 c15 =SUM(C11:OFFSET(C15,-1,0))

18 d11 =B11*C11

19 d15 =SUM(D11:OFFSET(D15,-1,0))

20 e11 =IF(E2,$B2,0)

21 e15 =SUM(E11:OFFSET(E15,-1,0))

The formulas shown are a result of GetFormula described on my Formula page.

COUNTIF and SUMIF formulas

A B C D E

1 Name Dept Salary Dept.Total Dept.Total

2 John Doe 01 A 40,000 =IF(COUNTIF($B$2:B2,B2)<>COUNTIF(B:B,B2),"",SUMIF($B$2:$B$93,B2,$C$2:$C$93))

3 John Doe 02 A 60,000 (use fill-handle to replicate formula downward)

4 John Doe 03 A 70,000 170,000

5 John Doe 07 B 60,000

6 John Doe 08 B 40,000

7 John Doe 11 B 40,000 140,000

8 John Doe 12 C 60,000

9 John Doe 13 C 60,000

10 John Doe 14 C 40,000

11 John Doe 15 C 70,000 230,000

12 Total 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
=A1<=SMALL($A1:$T1,(COUNTA(A$1:T$1)+1)/2)
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:
=AND($A1<>"",OR($A1=$A2,OFFSET($A1,-1,0)=$A1))
Duplicated Anywhere in Column: Need not be sorted
=COUNTIF($A:$A,$A1)>1
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
  Application.Volatile
  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
   Next
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)

A B C

1 St City -FM

2 AZ Phoenix KNAI

3 AZ Phoenix KKMR

4 AZ Phoenix KHOV

5 AZ Phoenix KHOT

6 AZ Phoenix KMRR

7 AZ Phoenix KOMR

8 AZ Douglas KRMC

9 AZ Douglas KRMB

10 AZ Nogales KNOG

11 AZ Yuma KYRM

12 CA Blythe KERU

13 CA L.A. KRCV

14 CA L.A. KRCD

15 CA Oxnard KXRS

16 CA Oxnard KXLM

A B C

1 St City -FM

2 AZ Phoenix KNAI

3 KKMR

4 KHOV

5 KHOT

6 KMRR

7 KOMR

8 Douglas KRMC

9 KRMB

10 Nogales KNOG

11 Yuma KYRM

12 CA Blythe KERU

13 L.A. KRCV

14 KRCD

15 Oxnard KXRS

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

Alternate-Row Shading in XL97 and above using filters. : =MOD(ROW(),2)=0 J.Walkenbach
Alternate-Row Shading using a macro -- [N/A deja archives]
AutoFilter (in addition to topics in this article, above)
- AutoFilter, Display AutoFilter Criteria in Excel (this PC World link is dead)
  Steve Bullen's code to show the criteria actually in use when Autofilter is in Effect. Article in PC World also has information on use of RANK when tied, and use of alternating stripes via conditional formatting.
- AutoFilter, Find Excel 2000 Data Quickly With AutoFilter -- MS Office, Update
- Date in advanced filter -- "<:"&TODAY() -- (see Date & Time for other date related information.
- Excel Filters -- Advanced Filter, Debra Dalgleish.
- Excel Filters -- Advanced Filter, Extract Data to Another Worksheet, Debra Dalgleish.
- ShowFilter function UDF to show filter used at top of column, Tom Ogilvy, 2002-01-17, programming; also see Displaying AutoFilter criteria (tip 44), John Walkenbach.
Color Palette -- the 56 Excel Colors
Color Stripes, Range, Conditional Formatting, Banding, Greenbar stripes (also see autofilter)
An example of coloring lines when the value in Column D (4th column) is negative. Some information on ranges. A quick look at conditional formatting available with XL97 and later.
Color Banding With Conditional Formatting also separate topics C.F., and it's subtopic C.F., Using cells on other sheets. -- Chip Pearson. Some specific examples spelled out by Ken Wright.
Color-Matching Blues [http://www.zdnet.com/pcmag/issues/1507/pcmg0103.htm]-- if this link fails to work then search on the title.
Combine rows, based on Column A posted by Patrick Molloy combines B column as comma separated values for rows that have same column A value (merging cells then deleting rows).
Conditional Formatting on this site, also see
David Hager's "Excel Experts E-Letter Archives" letters 3, 8, and 14.
(some techniques for those still on XL95 can be found at John Walkenbach's tip 25).
COUNT, Tip 52:Cell Counting Techniques, John Walkenbach, a nice page on range using: DCOUNT, COUNT, COUNTA, COUNTBLANK, COUNTIF, and more
Elapsed Time Counting Techniques for Date Ranges: COUNT, COUNTIF, SUM, SUMIF, counting
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.
Find Best Price With Excel INDEX and MATCH, Contextures Blog
Group and Outline -- Data|Group and Outline|Group (after you select your rows), see HELP.
Pivot Tables, in addition to Harald Staff's excellent Introduction To Pivot Tables already mentioned. (#pivottables). Note Harald has a macro at the end to REFRESH your pivot table, or same modified to be an event macro to refresh on double-click. p; (read about Event macros) variation of his macro as an Event macro.
- Pivot table topics at contextures.com index page «, Debra Dalgleish,
- Pivot table topics at DataPig Technologies index page «, Michael Alexander, consisting of Flash (videos) tutorial presentations. (is now a membership site)
- Excel 2000 Tutorial: PivotTable Reports 101, Microsoft Download example
- Microsoft Excel PivotTable Resource Center
- Debra Dalgleish's Pivot Tables and Charts « at Jon Peltier's site for introductory material and usage, or on her own site at Contextures for macros
- Pivot Table, Tutorial, Ed Ferrero
- Equal Length Cells per Column on this site has an example of a Pivot Table, main purpose of page is a macro to generate text for posting from a selection of cells.
- Tushar Mehta http://www.tushar-mehta.com in his RDBMS (Relational Database) area demonstrates relational database and then creates a Pivot table from the data base. Suggest going through the RDBMS Tutorial without annoying frames.
- Pet Peeves (an Excel wish list) by Aaron Blood,
  Perhaps not the best thing to identify a site, but it is unique. Rest of site looks interesting also.
  Pivot Tables, Memory errors in (Q1576486)
  Not enough memory to completely display PivotTable.
  Creating a database table from a summary table, tip 68, John Walkenbach, Reverse Pivot Table, creates a detail list that could produce the Pivot Table.
  Pivot Tables Links «, provided by Debra Dalgleish
SUMIF Examples
SUMIF, Tip 74:Summing and Counting Using Multiple Criteria
using SUMIF, COUNTIF, SUM that should solve most counting and summing problems. -- John Walkenbach
SUMPRODCT example of how SUMPRODUCT functions at Bob Phillip's xlDynamic.com
Tutorials, links to, on my Excel home page.

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

	A	B	C	D	E	F	G
1	Hotel	Rooms	Buy	Sell	01/15/1985	31062
2	Hotel 01	32	01/01/1973		TRUE	=AND(C2<=$E$1,OR(D2="",D2>=$E$1))
3	Hotel 02	20	01/14/1975	01/15/2000	TRUE	=AND(C3<=$E$1,OR(D3="",D3>=$E$1))
4	Hotel 03	15	01/26/1977	01/14/1983	FALSE	=AND(C4<=$E$1,OR(D4="",D4>=$E$1))
5	Hotel 04	100	02/08/1979	01/14/1999	TRUE	=AND(C5<=$E$1,OR(D5="",D5>=$E$1))
6	Hotel 05	30	02/20/1981		TRUE	=AND(C6<=$E$1,OR(D6="",D6>=$E$1))
7	Hotel 06	20	03/05/1983	01/31/2000	TRUE	=AND(C7<=$E$1,OR(D7="",D7>=$E$1))
8	Hotel 07	37	03/17/1985		FALSE	=AND(C8<=$E$1,OR(D8="",D8>=$E$1))
9	Hotel 08	57	03/30/1987	12/31/1999	FALSE	=AND(C9<=$E$1,OR(D9="",D9>=$E$1))
10	Room Tot.	311			TRUE	=1=1
11			Formula used in cell B10: =SUBTOTAL(9,C2:OFFSET(C10,-1,0))

	A	B	C	D
1	Checked	Value	Formula in Column A	comments
2	w	16
3	x	4
4	y	1
5		32
6	x	2
7	6		=SUMIF(A2:A6,"x",B2:B6)	simple formula
8	6		=SUMIF(A$2:OFFSET(A$8,-1,0),"x",B$2:OFFSET(C$7,-1,0))	simplifies insertion/deletion of rows
9	49		=SUMIF(A$2:OFFSET(A$9,-1,0),"<>x",B$2:OFFSET(C$7,-1,0))
10	2		=MATCH("x",A$2:OFFSET(A$10,-1,0))	position in range
11	x		=VLOOKUP("x",A$2:OFFSET(A$11,-1,0),1,FALSE)	Found 1st column value
12	4		=VLOOKUP("x",A$2:OFFSET(C$12,-1,0),2,FALSE)	found 2nd column value
13	3		=COUNTIF(A$2:OFFSET(A$13,-1,0),"<>x")
14	2		=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.

	A	B	C	D	E	F	G
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))

	A	B	C	D	E	F	G
1	NAME	program1	open	closed	program2	open	closed
2	JOE	shop	Jan-2002	Jan-2002	music	Jan-2002	Jan-2002
3	Fran	shop	Feb-2002		music	Feb-2002
4	J.R.	shop	Mar-2002	Mar-2002	music	Mar-2002	Mar-2002
5	Paul	shop	Apr-2002	Apr-2002	music	Apr-2002
6	Meg	shop	Apr-2002		music	May-2002	Mar-2002
7	Sara	shop	Apr-2002		music	Jun-2002	Apr-2003

	A	B	C	D	E	F	G	H	I	J
1	NAME	Started	Fini	All Done	Shop	Fini	Music	Fini	History	Fini
2	JOE	3	2	FALSE	Jan-02	Jan-02	Jan-02	Jan-02	Jan-02
3	Fran	3	3	TRUE	Feb-02	Jan-02	Feb-02	Jan-02	Jan-02	Jan-02
4	J.R.	0	0	TRUE
5	Paul	2	1	FALSE	Apr-02	Apr-02	Apr-02
6	Meg	2	1	FALSE	Apr-02				May-02	Mar-02
7	Sara	2	0	FALSE			Apr-02		Apr-02
8	Counts	12	7	FALSE	4	3	4	2	4	2
9
10	Started at least one			5
11	Started no courses			1
12	Finished no courses			2
13	Finished only one			2
14	Finished one or more			4
15	Finished all courses			1
16
17	B2	=COUNTA(E2,G2,I2)
18	C2	=COUNTA(F2,H2,J2)
19	D2	=B2=C2
20	B8	=SUM(B2:OFFSET(B8,-1,0))
21	C8	=SUM(C2:OFFSET(C8,-1,0))
22	D8	=B8=C8
23	E8	=COUNTA(E2:OFFSET(E8,-1,0))
24	F8	=COUNTA(F2:OFFSET(F8,-1,0))
25	D10	=COUNTIF(B2:B7,">0")
26	D11	=COUNTIF(B2:B7,"=0")
27	D12	=COUNTIF(C2:C7,0)
28	D13	=COUNTIF(C2:C7,"=1")
29	D14	=COUNTIF(C2:C7,">0")
30	D15	=COUNTIF(D2:D7,"True")-COUNTIF(B2:B7,"=0")

	A	B	C	D	E	F	G	H
1	Titles	Price	Count	M.Totals	AMY	ANN	BOB	SAL
2	Music.A	5.00	2	10.00	TRUE	FALSE	TRUE	FALSE
3	Music.B	6.25	3	18.75	FALSE	TRUE	TRUE	TRUE
4	Music.C	4.00	1	4.00	FALSE	TRUE	FALSE	FALSE
5	Music.D	7.00	2	14.00	TRUE	FALSE	FALSE	TRUE
6			8	46.75
7	c2	=COUNTIF(E2:L2,"=True")
8	d2	=B2*C2
9
10		Price	Count	M.Totals	AMY	ANN	BOB	SAL
11	Music.A	5.00	2	10.00	5.00	-	5.00	-
12	Music.B	6.25	3	18.75	-	6.25	6.25	6.25
13	Music.C	4.00	1	4.00	-	4.00	-	-
14	Music.D	7.00	2	14.00	7.00	-	-	7.00
15			8	46.75	12.00	10.25	11.25	13.25
16	c11	=COUNTIF(E11:L11,">0")
17	c15	=SUM(C11:OFFSET(C15,-1,0))
18	d11	=B11*C11
19	d15	=SUM(D11:OFFSET(D15,-1,0))
20	e11	=IF(E2,$B2,0)
21	e15	=SUM(E11:OFFSET(E15,-1,0))

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