According to the Brits, the ’50s and ’60s were the golden age of cinema.
I copied the data from BFI site into Excel. I did a little data manipulation to get this list.
My QuickTTC addin came in handy as there is an ASCII 0160 character in there and it split on it nicely. I added the Date column with the formula =DATE(D2,1,1) to see if I could group on decade in a Pivot Table. I couldn’t. So I added the Decade column with this formula =FLOOR(YEAR(F2),10).
From here, you would probably use a Pivot Table/Pivot Chart. Not me. Pivoting is great for analyzing, but I don’t much care for it for reporting and presentation. So I used my MakeUniqueList macro to get a unique list of decades on a new sheet. Next I counted the films and summed the votes like so:
I do this all time, probably because my MakeUniqueList macro creates a new sheet. The normal way to build a SUMIF formula goes like this:
- =SUMIF(
- Switch sheets
- Select first range, F4
- Switch back to the formula sheet
- Select criteria range
- Switch back to the data sheet
- Select the sum range, F4
- Close paren and enter
and you get =SUMIF(Sheet2!$G$2:$G$53,Sheet5!A2,Sheet2!$E$2:$E$53). I don’t like all that sheet switching and I don’t like unnecessary sheet references in my formulas. Yes, I’m particular. My normal method of creating a SUMIF goes like this:
- =SUMIF(
- Switch sheets
- Select first range, F4
- Type ,1, as a placeholder for the criteria
- Select the sum range, F4
- Close paren and enter
- F2 to edit the formala
- replace the ‘1’ with the cell reference
That’s more palatable to me. Only one sheet switch, but there is a little editing at the end. Never satisfied, I developed this little gem to remove some of the drudgery:
Private Sub mxlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count = 1 Then
If Target.HasFormula Then
If Target.Column > 1 Then
If IsIf(Target.Formula) Then
Application.EnableEvents = False
Target.Formula = Replace(Target.Formula, ",1", "," & Target.End(xlToLeft).Address(False, False))
Application.EnableEvents = True
End If
End If
End If
End If
End Sub
Private Function IsIf(ByVal sFormula As String) As Boolean
Dim bReturn As Boolean
Const sSUMIF As String = "=SUMIF("
Const sCOUNTIF As String = "=COUNTIF("
bReturn = True
bReturn = bReturn And (Left$(sFormula, Len(sSUMIF)) = sSUMIF Or Left$(sFormula, Len(sCOUNTIF)) = sCOUNTIF)
bReturn = bReturn And InStr(1, sFormula, ",1") > 0
IsIf = bReturn
End Function
These procedures live in my UIHelpers.xla file. I don’t use the Personal Macro Workbook. Instead I have a few addins that separate my procedures by their function or scope of use. That’s why my event procedure above isn’t the typical SheetChange event. It’s in a custom class module with an Application property declared WithEvents. That way it will work on any open workbook.
On to the code: I only want to do the deed when I’m editing one cell, so I check that the Target is a one cell range with the Count property. Next I exclude any entries that aren’t formulas. I’m assuming that my criteria cell is somewhere to the left of the formula I’m entering, so I don’t do anything on formulas entered in column 1 because that’s as left as you can get. My final criteria comes from the custom function IsIf, generally check that it’s a SUMIF or COUNTIF. The custom function makes sure it starts with one of those two functions and also determines that I put the “,1” placeholder in there.
If all that passes, the placeholder is replaced with the cell reference to the left of the formula cell – all the way to the left if there are several contiguous columns. That may not always be right, but it will be most of the time.
Now I can enter in Sheet5$C2
=SUMIF(Sheet2!$G$2:$G$53,1,Sheet2!$E$2:$E$53)
and it automagically turns into
=SUMIF(Sheet2!$G$2:$G$53,A2,Sheet2!$E$2:$E$53)
If I ever want to make a SUMIF formula with a hardcoded 1 as the criteria, well, I’m screwed.
