How to count Categories in an Excel table. SUMPRODUCT takes 45 minutes to calculate.

You're effectively doing the same calculation 50 times for each ReviewID. Why not just calculate for the first row in each ReviewID, i.e. change the O2 formula to this:

=IF(A2<>A1,SUMPRODUCT(($B$2:$B$65000=B2)*($C$2:$C$65000=C2)*($G$2:$G$65000=G2)),"")

which will calculate in roughly 1/50th of the time.

You can filter on column O by non-blanks if you just want to see a summary of the results....

regards, barry

I've gotten the recalc time down to 5 seconds on my laptop.

1) Sort the data, first by column B, then by column C, then by column G. This took about 10 seconds.
2) Use the same concatenation formula as previously suggested in column O. Copying it down was instantaneous.
=B2 & "|" & C2 & "|" & G2

3) Put the following COUNTIF formula in cell P2, then copy it down. Since it only does the COUNTIF once for each unique combination, it is much faster.
=IF(O2=O1,P1,COUNTIF(O$2:O$65000,O2))

Incidently, the trick to copying down a formula without laboriously dragging it down is this:
A. Put the formula in the first cell
B. Select that cell
C. Double-click the little square at the bottom right corner of the selection marquee. The formula will then be copied down until the first blank cell in the adjacent column.

Brad

megnin,
Here is a macro that will count the number of combinations (user picks the columns) using the approach described in my last post. It does the following:

1) Ask the user to pick the columns to count combos of
2) Stores the original data order
3) Creates the concatenation formula in an empty column
4) Sorts by the concatenation formula
5) Creates the counting formula in an empty column
6) Replaces the formulas with their values
7) Restores the original data order
8) Deletes the auxiliary columns

When all is done, the count of the desired combinations is listed to the right of your data

To install a sub in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

Optional steps to assign a shortcut key to your macro:
8) Repeat steps 5 & 6, then press the "Options" button
9) Enter the character you want to use (Shift + character will have fewer conflicts with existing shortcuts)
10) Enter some descriptive text telling what the macro does in the "Description" field
11) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.


Brad

Sub UniqueComboCounter()
'Counts the number of times each unique combination of inputs occurs in a table. User selects which inputs to use.
Dim ar As Range, cel As Range, rgAux As Range, rgCombos As Range, rgSort As Range
Dim frmla1 As String, frmla2 As String, sLabel As String
On Error Resume Next
Set rgCombos = Application.InputBox("Please pick the header labels you want to count combinations of.", "Control click if necessary", Type:=8)
On Error GoTo 0
If rgCombos Is Nothing Then Exit Sub 
Application.ScreenUpdating = False
Set rgSort = ActiveSheet.UsedRange
Set rgAux = rgSort.Columns(1).Offset(0, rgSort.Columns.Count).Resize(, 3)
Set rgAux = rgAux.Offset(1, 0).Resize(rgAux.Rows.Count - 1)
For Each ar In rgCombos.Areas
    For Each cel In ar.Rows(1).Cells
        frmla1 = frmla1 & " & ""|"" & RC" & cel.Column
        sLabel = sLabel & Chr(10) & Intersect(cel.EntireColumn, rgSort.Rows(1))
    Next
Next
frmla2 = "=IF(RC[-1]=R[-1]C[-1],R[-1]C,COUNTIF(RC[-1]:R" & (rgAux.Row + rgAux.Rows.Count - 1) & "C[-1],RC[-1]))" 
rgAux.Columns(1).Formula = "=ROW()"
rgAux.Columns(1).Formula = rgAux.Columns(1).Value
rgAux.Columns(2).FormulaR1C1 = "=" & Mid(frmla1, 10)
ActiveSheet.UsedRange.Sort key1:=rgAux.Cells(1, 2), order1:=xlAscending, header:=xlYes
rgAux.Columns(3).FormulaR1C1 = frmla2
rgAux.Columns(3).Formula = rgAux.Columns(3).Value
rgAux.Cells(0, 3) = Mid(sLabel, 2)
rgAux.Cells(0, 3).WrapText = True
ActiveSheet.UsedRange.Sort key1:=rgAux.Cells(1, 1), order1:=xlAscending, header:=xlYes
rgAux.Columns(1).Resize(, 2).EntireColumn.Delete
Application.ScreenUpdating = True
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33: