SUMPRODUCT -- count where two sets of criteria are true

I found this old note from 2013 in my files. SUMPRODUCT is a very useful formula, but not one I have used very much. Putting this here as a reminder of what it can do -- even though my example is probably not very helpfully written.

COUNTIF is fine where you want to count the number of items in one column that meet one criterion. But what if you have two columns and two sets of criteria? SUMPRODUCT.

See http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

(It's possible COUNTIFS might work for this example too: http://spreadsheets.about.com/od/excelfunctions/qt/20080127.htm)

Example: Teaching Allocations, elective units (LLB/JD)

Reduce manual data entry with online forms

This page was originally used to test Adobe FormsCentral, which is a genius solution to an efficiency problem. Previous web forms have just sent the input data to me in an email. To use that data it had to be copied and pasted into a spreadsheet, which was just silly. Or alternately I had people email information to me -- which then had to be manually transferred into a spreadsheet to be manipulated etc.

Online Forms take the data straight from the user's submission into a spreadsheet. You can allow file attachments and set automated emails to the submitter and whoever in your workplace needs to be alerted for action.

See for example: http://sydney.edu.au/law/about/working_with_us.shtml

Update November 2015:
Adobe closed FormsCentral, and that is very sad. After some research we replaced it with FormStack, which is not quite as easy to use as Adobe and design-wise is not as powerful. But it still has the essential feature of allowing attachments on forms, as well as automated email notifications and the like. It integrates with Google Docs and Google Drive -- vital because FormStack doesn't have a bulk download capability for attachments. So I have the attachments automatically send to Google Drive, putting the surname and unique ID as a filename prefix. For easy copy & paste of the data table I have the form submissions go straight into a Google Spreadsheet.

A bit more fiddly from a back-end perspective, but just as easy from a user point of view. And once you've done the setup it saves you a mountain of time.

Make your "email me" links more useful with automatic subject line and body text

When I send out committee meeting agendas I include a link that members can click to give apologies. To make this easier for them, and easier for me, I set the link up to automatically populate the subject line and body text. Why?

• Easier for recipients: they just have to click a link then press "send" on their email.
• Easier for you: all the emails will come back with the same subject line.
• Advanced: you can insert prompts such as "dietary requirements" in the body text.

The instructions walk you through creating an example link:

To:

your email address

Subject:

RSVP for 1 May meeting

Body text:

Dear Sarah,

I will attend the meeting on 1 May.

Kind regards.

Creating this kind of link in Outlook or Word is quite easy, using the "edit hyperlink" feature and the %body= tag. See the attached PDF with screenshots for instructions.

View the PDF on Google Drive

Annual timesheet template v2014

At the end of 2012 I turned a 4-weekly timesheet into an annual timesheet tracker. A number of issues popped up early on -- a really simple oversight on a late-requested addition -- which I had to provide a quick fix for. But I'm now in the process of improving it for 2014.

2013 features

• Protected sheets to prevent accidental messing up of formulas. (But not password protected because I trust that anyone intentionally editing it knows what they're doing. Do unto others...)
• Macros to unprotect / re-protect all sheets. (Excel 2010 doesn't seem to have a "protect all" option, so you otherwise have to lock all 13 sheets individually. Inefficient.) CTRL+SHIFT+U and CTRL+SHIFT+L.
• Summary page showing hours worked, hours accrued per month, and # of SDOs (scheduled days off, probably also known as time off in lieu days, rostered days off etc). This is to make it more useful as a tool for supervisors/managers.
• Graphs! (I don't know how useful they are, but it impresses some people -- important for adopting a new thing.)