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)

Table format

Sem	Unit name	Class type	Teacher
1	Advanced Constitutional Law	(blank)	Gerangelos, Peter
	Advanced Criminal Law	(blank)	Loughnan, Arlie
	Advanced Evidence	(blank)	Hamer, David
	Advanced Public International Law	Co-teaching	Brown, Chester
			Crock, Mary
	Australian Income Tax	(blank)	Burch, Micah
	Banking & Financial Instruments	(blank)	Magnusson, Roger

I use a PIVOT TABLE to grab all of the units which are LLB/JD Electives, using the unit level as a pivot table filter. That leaves me with a table divided by semester. Each time a unit is added or removed, the number of units changes. This is one challenge of PIVOT TABLES -- it is hard to put formulas in added columns because the data in the rows frequently changes (at least in the Teaching Allocations, which is a living document). I used to do a rough "COUNTA" in an added column to add the number of units in semester 1 and semester 2, but each time the data changed the count had to be updated. I can't just count the unit name because some of the units would go across two or three lines if there are co-teachers, two groups etc.

But I discovered today that I could use a SUMPRODUCT to count the number of units on offer in a semester. Here's a breakdown.

Step 1: Choose "repeat item labels"

Step 1: Set pivot table to repeat item labels in the Sem column.

• ·     Right click on the Sem column à Field Settings.
• ·         Go to Layout and Print tab à Repeat item labels

Step 2: Add a column with a formula which checks whether the Unit Name column has text. I used " =ISTEXT(B4)", though you could use "ISBLANK" as well and reverse the criterion in the SUMPRODUCT formula.

Step 3: Use SUMPRODUCT formula.

• ·         =SUMPRODUCT(--(G4:G100=TRUE),--(A4:A100=1),A4:A100)
• ·         --(G4:G100=TRUE): Check the array column G (my TRUE/FALSE of whether the Unit Name contains text or blank) to test criterion TRUE.
• ·         --(A4:A100=1): Check the array in column A (Semester) to test criterion 'cell = 1'.
• ·         Where both criteria are true (in the same row), add the rows in column A.

3b: For testing semester 2 I just changed the SUMPRODUCT formula to check if col A = 2, and then because it returns the SUM of column A in those rows, my whole SUMPRODUCT formula had to be divided by 2 to return a count.

Step 4: To make everything neater, I added a "CONCATENATE" formula in the blank top row and hid the columns with my working. =CONCATENATE("Semester 1 = ",F4," electives; ","Semester 2 = ",F5," electives")

BEFORE

Level	LLB/JD Elective
Sem	Unit name	Class type	Teacher			B text?
1	Advanced Constitutional Law	(blank)	Gerangelos, Peter	Sem 1	30	TRUE
1	Advanced Criminal Law	(blank)	Loughnan, Arlie	Sem 2	24	TRUE
1	Advanced Evidence	(blank)	Hamer, David			TRUE
1	Advanced Public International Law	Co-teaching	Brown, Chester			TRUE
1			Crock, Mary			FALSE
1	Australian Income Tax	(blank)	Burch, Micah			TRUE
1	Banking & Financial Instruments	(blank)	Magnusson, Roger			TRUE
1	Chinese Laws and Chinese Legal Systems	Offshore	Bath, Vivienne			TRUE
1	Commercial Land Law	(blank)	Burns, Fiona			TRUE
1	Death and Inheritance Law	(blank)	Burns, Fiona			TRUE

AFTER -- concatenated text added to second row, to summarise the data

Level	LLB/JD Elective
	Semester 1 = 30 electives; Semester 2 = 24 electives
Sem	Unit name	Class type	Teacher
1	Advanced Constitutional Law	(blank)	Gerangelos, Peter
1	Advanced Criminal Law	(blank)	Loughnan, Arlie
1	Advanced Evidence	(blank)	Hamer, David
1	Advanced Public International Law	Co-teaching	Brown, Chester
1			Crock, Mary
1	Australian Income Tax	(blank)	Burch, Micah
1	Banking & Financial Instruments	(blank)	Magnusson, Roger
1	Chinese Laws and Chinese Legal Systems	Offshore	Bath, Vivienne
1	Commercial Land Law	(blank)	Burns, Fiona
1	Death and Inheritance Law	(blank)	Burns, Fiona