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.
Shouldn’t we be using Structured references or Dynamic Named Ranges ?
I don’t like the sheet switching and the unnecessary sheet references either. I do the following:
1. =SUMIF(,
2. Select criteria range then type a comma =sumif(,A2,
3. Switch to the data sheet
4. Select the sum range then type a close paren =sumif(,A2, Sheet2!$E$2:$E$53)
5. Put the cursor to before the first paren
6. Select the range for the first argument =sumif(Sheet2!$G$2:$G$53,A2, Sheet2!$E$2:$E$53)
7. Press Enter
Agree with Sam.
I usually use VBA to create dynamic named ranges for each column, my headers, and the numerical data. Then just refer to those in the SUMIF formula. This becomes even more beneficial using Excel 2010, since I only need to type the first couple of characters of the named range, and then use Intellisense to select which one I want (without a single sheet switch or mouse movement in sight.
That’s a good one Rob.
As for named ranges and structured references, I agree. About half the time I’m using SUMIF, it’s a one-off quick-and-dirty thing (like charting films), so the extra setup has not payoff for me.
The thing I hate most about sheet switching is that if you have to go back to the sheet you started with, it starts prefixing cell references with Sheet1!
re constant 1 as criteria, =SUMIF(…,”=1″,…)
Why not just use a macro to replace unnecessary sheet references? It’d be a lot more general than a special purpose macro just for SUMIF.
Why not use DCOUNT and DCOUNTA. This would elimimate having cell references in formulas (Sheet2!$G$2:$G$53).
This will allow flexibility for database expansion without changing the formula.
@fzz: I’m considering the exact same thing, because I do hate those unnecessary references.
Has anybody coded such a macro?
Is there a drawback I (we) miss?
Replace(Target.Formula,me.Name & "!","")That seems dangerous.
Your macro rocks, but I still have an impression that this could have been done in an easier way using pivot table, as you foretold. Anyways it will help the readers to have a stronger understanding of sumif and countif functions. :)
Awesome! Works like a charm in Google sheets! :)