SumProduct Function Applications

SumProduct Function Applications

When teaching Excel classes students occasionally ask about the SumProduct function. There are many aspects of SumProduct that mirror the SumIfs functions capabilities but SumProduct can produce different answers when combined with the unary operator. This tutorial is not meant to be a thorough discussion of SumProduct but is meant to provide an addendum to class notes.

Click Here To Download A Sample Workbook

I have provided a picture of the worksheet for those who cannot open Excel workbooks:
Sumproduct

SumProduct – Example 1

The SumProduct function multiplies one array by another array. An array is typically a range of cells in a single column. In the first example imagine that our goal is to calculate the total gross pay for all employees in all divisions and departments. The ‘old fashioned’ Excel approach is to write a formula to calculate hours * rate for each employee (cells G2:G21) then Sum those values. In this Level 1 example, we would need to generate 20 formulas in 20 cells to find the answer, where a SumProduct function calculates the result with less effort.

With SumProduct
=SUMPRODUCT(E:E,F:F)

Without SumProduct
=(E2*F2)+(E3*F3)+(E4*F4)+(E5*F5)+(E6*F6)+(E7*F7)+(E8*F8)+(E9*F9)+(E10*F10)+(E11*F11)+(E12*F12)+(E13*F13)+(E14*F14)+(E15*F15)+(E16*F16)+(E17*F17)+(E18*F18)+(E19*F19)+(E20*F20)+(E21*F21)

SumProduct – Example 2

About fifteen years ago Excel did not offer a SumIfs function and SumProduct was an alternative to using an array formula. Like the SumIfs function, SumProduct can work conditionally using criteria. Where SumIfs only allows AND criteria, SumProduct also supports OR criteria (but this generally double counts and generates incorrect results). Several studies have shown that SumIfs functions calculate significantly faster than SumProduct functions.

In example 2 imagine that we want to only sum the gross pay if the division equals Maine and the department equals Sales. In this example the asterisk (*) between Array1 and Array2 operates as an AND operator. The formula reads, If column C contains “Maine” AND Column D contains “Sales”, then Sum the value in column E times column F. If we use a plus (+) instead of an asterisk, this represents an OR criteria but double counts the products of Maine and Sales.

With SumProduct
=SUMPRODUCT((C:C=”Maine”)*(D:D=”Sales”),E:E,F:F)

With SumIfs
=SUMIFS(G:G,C:C,”Maine”,D:D,”Sales”)

Array Formula Sum If
{=SUM(IF((C:C=”Maine”)*(D:D=”Sales”),E:E*F:F))}

SumProduct – Example 3

At times we see a double hyphen (- -) in a formula. In Excel the double hyphen is known as a unary operator. The purpose of the unary operator is to force text values to 0/1 values where zero means false and one means true.

SumProduct Formula
=SUMPRODUCT(–(C:C=”Maine”)*–(D:D=”Sales”),E:E,F:F)

Like example 2 except now the – operator converts values where a cell contains Maine or Sales to 0 for false and 1 for true, then uses the corresponding 1 values to determine which values in columns E and F are multiplied and summed.

SumProduct is also used to compare values in two columns to count how many words or numbers match (typically columns in separate workbooks). In general, do not sort the data, this technique compares corresponding values within the same row.

SumProduct Formula
=SUMPRODUCT(–($C:$C=”Maine”),–($I:$I=”Maine”))

This formula asks Excel to compare the values in column C to the values in column I for the word Maine. The unary operator converts results to 0 for false or 1 for true, and then the SumProduct function sums the values for 1/true.

SumProduct can also compare the value in two columns to count identical values

SumProduct Formula
=SUMPRODUCT(–(E:E=40),–(K:K=40))

Like the previous example, this formula asks SumProduct to sum the 1 values if the numbers in column E and K are identical and equal 40.

SumProduct – Example 4

The most valuable use of the SumProduct is to calculate a weighted average (this question only occurs about once every ten years). The straight average sums the values and divides by the count. A weighted average considers some values have a higher weight. In this example imagine the weighted average is based on the count of Divisions, where there are six Connecticut values, four Maine values, and five New Hampshire and five Vermont values.

In this example the SumProduct weights the average gross pay based on the count of divisions.

SumProduct Formula – Longhand notation for weighted average
=SUMPRODUCT({780,780,860,504,589,504,350,213,860,589,472.15,176,860,880,443.75,880,472.15,780,840,350},{4,6,4,6,5,6,5,6,5,6,5,5,5,5,5,5,6,4,4,5})/SUM(H2:H21)

SumProduct Formula – Shorthand notation for weighted average
=SUMPRODUCT(E2:E21,F2:F21,H2:H21)/SUM(H2:H21)

Excel Tutorial – Vlookup Multiple Columns To The Right With An Array Formula

In the process of teaching Excel classes, many students ask:

How can I write a single vlookup function that returns multiple values to the right, instead of a single value, which causes me to write many vlookup functions in many columns.

This tutorial does not teach vlookup, that feature is taught in the Introduction and Advanced classes. Here we focus on vlookup and array formulas. The purpose of this tutorial is to supplement the class notes.

When performing calculations in Excel, typically a single cell contains a formula with functions that return a single value. An array formula is different in that it will typically include many cells (rows or columns) and return many values. Where I would teach students to press the enter key to commit an edit, to create an array formula we are required to hold Ctrl+Shift+Enter.
To follow along with this example, please download a workbook with this link:

Vlookup Array Workbook

Though not required, I suggest when you open this workbook, click the Enable macros command which will create two windows and tile them side by side.
In this example, the idea is to write a single vlookup function on the VlookupOrders sheet. The single vlookup will find the order number on the Data sheet and return three values, the Company Name, the Country and the Sale.

Example Of Data In Vlookup Array Workbook

vlookup array formula purpose

1) On the VlookupOrders worksheet tab, select/highlight cells C2:E2
2) Create the following formula:
=VLOOKUP(B2,Data!B:F,{3,2,5},0)

vlookup array formula purpose

Note: For the third argument, the col_index_num, we are allowed to use as many values as needed. In this example I am trying to illustrate that as much as I can return three columns to three cells — in any sequence — we can choose any number of columns as needed. However, to vlookup four columns, in step 1, select/highlight four cells. Also note that we cannot simply add parentheses around the values of the col_index_num, use curly braces {} (squiggly brackets – typically found to the right of the letter P on your keyboard).

3) To convert this to an array formula, hold the Ctrl+Shift keys and press the Enter key. The array formula adds {} (curly braces) around the vlookup formula.

A final word of warning: Array formulas have drawbacks. One drawback is that array formulas recalculate a bit slowly over thousands of rows. A bigger drawback is that we have lost the ability to insert a new column within the arrayed formula range. To verify the loss of the ability to insert a column inside the array, on the VlookupOrders sheet, right click column D and left click the Insert command.

Excel Class VBA Macro Adds IfError Functions To Every Formula

Excel Class VBA Macro Adds IfError Functions To Every Formula

Excel VBA MacroToday a student asked Excel Class if there was a macro to automatically add an IfError function to every formula on every sheet in a selected workbook, assuming there was not already an IfError function used.

On short notice the Excel Class instructor provided the following code which we wanted to share with our readers.

Remember that macros have no Undo command. Once you run this VBA macro the only undo is to close the file without saving changes. The macro leaves a selected workbook open so you can review the changes before deciding to save or not.

To use this code, press the Alt+F11 key to open the VB Editor.
Select the Insert menu > Module command.
Then paste the following code into the empty window:
To run the program, press the F5 key, or from Excel select the Ribbon View Tab > Macros button and run the AddIfError macro.

Public Sub AddIffError()
‘purpose: IF there is a formula add iferror
‘if there’s an iferror already, ignore the formula
‘process all sheets in wb

‘Handle Errors
On Error GoTo Errorhandler

‘Step 10: Ask user to open a wb
MsgBox “Please click OK to select a workbook to process”

‘Step20: Prompt user to open wb to process
With Application.FileDialog(msoFileDialogOpen)
.Show
.Execute
End With

‘Step30: On every sheet add iferror
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets

If ws.Visible = True Then ws.Select

‘Select all cells
Range(“A1”).Select
ActiveCell.CurrentRegion.Select

Dim r As Range
For Each r In Selection
If Left(r.Formula, 1) = “=” Then
If Left(r.Formula, 8) = “=IFERROR” Then
‘Do Nothing
Else
Dim myformula
myformula = Right(r.Formula, Len(r.Formula) – 1)
r = “=IfError(” & myformula & “,0)”
End If
End If
Next r

Next ws

Exit Sub
Errorhandler:
If Err.Number <> 0 Then
MsgBox “Something went wrong”
End If

End Sub

If you want to learn more about Excel VBA programming check out our 2-day Excel VBA course in New York City.

Report Hidden Rows With An Excel VBA Macro

Report Hidden Rows With An Excel VBA Macro

find hidden rows with vbaMany Excel users choose to hide a row of data on a worksheet for a variety of reasons. When Excel users collaborate it’s difficult to know that a row has been hidden without careful visual inspection of every row. If a hidden row contains zero values there is no issue. However, if a hidden row contains non-zero values it’s an issue to be concerned about.

Excel has a feature to report that rows may be hidden and then offer to unhide them. However the Excel feature does not tell us which sheets or which rows were hidden or unhidden. To use the Excel feature select the File Tab > Info > Check for Issues > Inspect Document command.

Excel’s Sum function does not ignore hidden rows, which is why Microsoft recommends we use the Subtotal function with the 109 function_number argument instead.

The subtotal function might read:
=SUBTOTAL(109,cell_range)

To help our students produce a report of hidden rows, Excel Class is pleased to share a program that will report if your workbooks have hidden rows. Realize that the speed at which this program operates is proportional to the number of sheets and the number of rows of data per sheet to examine. Even in a large workbook the program should finish in under 5 minutes. However, if the program appears to fail you can press and hold the Esc key on your keyboard to cancel execution.

Simply:

  1. Copy the code in the courier font below into a blank new workbook, from the Option Explicit to the End Sub at the very bottom;
  2. In any Excel workbook: Hold the Alt key and tap the F11 function key to open the VB Editor.
  3. Select the Insert menu > Module command.
  4. Paste the copied code into the empty white code window.
  5. To run the program, close the VB Editor and switch back to Excel.
  6. Select the View tab of the ribbon, then click the Macros button to the far right (or choose the View Macros command).
  7. Select the FindHidden program and click the run button.

The program will prompt you to open a workbook to inspect.

Option Explicit
Dim msg ‘msgbox prompt
Dim ws As Worksheet
Dim wb As Workbook
Dim r As Range
Dim RowCount ‘How many rows based on .xls or .xlsx
Dim RowCounter
Dim Sheet_Counter
Dim HasValues As Boolean
Dim Exists As Boolean
Dim Report As Boolean
Dim LastRow As Long
Dim LastCol As Long

Public Sub FindHidden()
‘Purpose: Ask user to open a workbook to examine;
‘look at every sheet, if any row is hidden, add a report sheet
‘detailing the sheet name, row(s) hidden, and if they contain
‘non zero values

‘Error Handling
‘On Error GoTo errorhandler

‘Speed It Up
With Application
.ScreenUpdating = False
.MultiThreadedCalculation.Enabled = True
End With

‘Step 10: Ask user to open a workbook to examine
msg = “Please choose a workbook to examine”
MsgBox prompt:=msg, Buttons:=vbExclamation + vbOKOnly, Title:=”Select a workbook”

With Application.FileDialog(msoFileDialogOpen)
.Show
.Execute
End With

Set wb = ActiveWorkbook
wb.Activate

‘Step 20: Determine file format for row count
If Len(wb.Name) – InStr(1, wb.Name, “.”) = 3 Then
RowCount = 65535
Else
RowCount = 1048576
End If

‘Step 30: Loop through sheets
For Each ws In wb.Sheets

‘Select the sheet if not hidden
If ws.Visible = True Then
ws.Select
Else
GoTo Skip_This_Sheet
End If

‘Step 35: Find the last row and last column
Call Find_End_Of_Data

‘Step 40: On any sheet if a row is hidden report it

For RowCounter = 1 To LastRow
If Rows(RowCounter).Height = 0 Then

‘Set a report variable to report hidden row
Report = True

‘Reset hasvalues variable
HasValues = False

‘Determine if hidden row contains a non-zero value
If Application.WorksheetFunction.Count(Rows(RowCounter)) > 0 Then
HasValues = True
End If

‘Step 50: Make a report
‘Find if HiddenRowReport sheet exists

Exists = False

For Sheet_Counter = 1 To wb.Sheets.Count
If wb.Sheets(Sheet_Counter).Name = “HiddenRowReport” Then
Exists = True
Exit For
End If
Next Sheet_Counter

‘If no HiddenRowReport sheet, add it and name it
If Exists = False Then
wb.Activate
wb.Sheets.Add before:=Sheets(1)
wb.Sheets(1).Name = “HiddenRowReport”
wb.Sheets(“HiddenRowReport”).Range(“A1”) = “Sheet Name”
wb.Sheets(“HiddenRowReport”).Range(“B1”) = “Row”
wb.Sheets(“HiddenRowReport”).Range(“C1”) = “Contains Non-Zero Values?”
End If

‘Find last row, offset and report it
wb.Sheets(“HiddenRowReport”).Select
‘Range(“A” & rowcount).End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell = ws.Name
‘Autofit column
ActiveCell.EntireColumn.AutoFit
ActiveCell.Offset(0, 1) = RowCounter
‘Autofit column
ActiveCell.Offset(0, 1).EntireColumn.AutoFit
ActiveCell.Offset(0, 2) = HasValues
‘Autofit column
ActiveCell.Offset(0, 2).EntireColumn.AutoFit
End If

‘Go back to sheet being searched for hidden rows
ws.Select

Next RowCounter
Skip_This_Sheet:
Next ws

‘Step 60: Message if a report was created
If Report = True Then
MsgBox “There is a report of hidden rows”
wb.Sheets(“HiddenRowReport”).Select
Else
MsgBox “No Hidden Rows Were Found”
End If

Exit Sub

errorhandler:

If Err.Number <> 0 Then
MsgBox “There was a problem”
ActiveWorkbook.BuiltinDocumentProperties(“Comments”) = Err.Number & ” ” & Err.Description
End If

End Sub

Public Sub Find_End_Of_Data()
LastRow = Range(“A1”).SpecialCells(xlCellTypeLastCell).Row + 1
LastCol = Range(“A1”).SpecialCells(xlCellTypeLastCell).Column + 1
End Sub

Data Tables And Interactive Charts

The Excel Class blog provides an opportunity to supplement the material we teach in our class. This tutorial assumes our readers are familiar with named ranges. (See: Advanced Excel Class)

Excel’s data table feature is a what-if analysis tool that allows users to create tables (rows/columns) of results based on a one or two variable formula. Data tables are useful for simple calculations like future value tables or mortgage payment tables to examine different scenarios based on varying input assumptions. Our previous post examined array formulas with vlookup (Vlookup Array Formula Tutorial) and data tables are an extension of the discussion of array formulas.

Download the finished workbook here

In the following example we use a data table to show rates of return for an investment and then use the data table as the basis of an interactive chart using an offset function in an array formula.

The formula for Future Value is:

FV = PV * (1 + i) ^ t

Where:

FV means future value

PV means present value

i is the interest rate

t is time

To Create A Data Table

1. Set up variable inputs, optionally (recommended) name the cells to use in the formula;
Data Table Step 1

2. To the right of the PV label, in cell B1, type a number like 1. In this example 1 means $1, our present value.

3. In a separate cell, create the formula that references the named ranges. In this example the formula appears in cell C4 which will become the upper left corner of the data table;
Data Table Step 2

4. To the right and below the formula, add variable inputs;
Data Table Step 3

5. Select the range to use for the data table, including the formula in the upper left corner;
In this example select cells C4:K24;

6. On the ribbon, select the Data tab > What If Analysis > Data Table command;
Select a row input cell (refer to a cell with a name); In this example, cell B2 is the row input (interest).
Select a column input cell (refer to a cell with a name); In this example, cell B3 is the column input (time).
Click the OK button to generate the data table.
Data Table Step 5

Note: The formulas produced by the data table in cells D5:K24 are array formulas. Be aware that array formulas remove the ability to insert or delete a row or a column inside the data table.

To Create An Interactive Chart Based On The Data Table

1. In cell C26, type a 0 (zero);

2. Select (highlight) cells D26:K26;

3. Type an = (equals) sign;

Enter the following formula:
=OFFSET(D5:K5,C26,0)

To create an array formula, hold the Ctrl+Shift keys and press the Enter key.

The offset function offsets the range D5:K5 by the value in cell C26 (zero rows)
Interactive Chart Array Formula

4. To add an interactive spinner control, show the Developer tab of the ribbon. To show the Developer tab, right click any tab of the ribbon, choose the Customize Ribbon… command. In the Customize Excel Options dialog, check the Developer tab.

5. On the Developer tab of the ribbon, select the Insert > Form Controls > Spinner command.
Developer Tab Insert Spinner Command

6. Click and drag a small spinner control in a cell to the left of cell C26.

7. Right click the spinner control, left click the Format Control command.

In the Format Control dialog:

Set the minimum value to 0

Set the maximum value to 20 (the number of years in column C)

Set the link cell to cell C26
Developer Tab Insert Spinner Command

8. The final step is to create the interactive chart.

Select cells D26:K26

On the ribbon, select the Insert tab > 2D Line Chart command
Insert 2D Line Chart

9. To see the chart become interactive, click the up arrow on the spinner control to increase the value in cell C26.

Note that as the value increases the offset function plots different rows of the data table in the chart.

Final note: I am obligated to give credit to Tushar Mehta for his excellent charting tutorials. Learn more about Tushar’s chart tutorials here: http://tushar-mehta.com/excel/charts/ In fact, I always encourage Excel students to study Tushar’s tutorials on: 3D Surface Charts, Normal Distribution (Bell Curves), Waterfall (Cash flows), and Values That Differ By Magnitude.

Excel Class Launches Online VBA Training

Excel Class receives requests for Microsoft Excel VBA training courses from every corner of the country. We are pleased to announce that our new exclusive series: The Excel VBA webinar is now available online.

Our training sessions begin once a month at different start times. Each session lasts two hours and includes no less than 30 minutes of homework to practice between sessions. In total the classes run for 12 weeks. Since we realize you may not be able to attend 12 consecutive weeks we allow you to retake the class an unlimited number of times and you can start in one class and skip into others classes (by providing us with advance notice). We offer exclusive video libraries where students can log in to a secured area to review videos of your class and download example workbooks.

Though our New York class runs for 28 hours, our webinar is spread across 48 hours to allow more time to review homework, provide time for questions and answers, and to go into further detail.

Click here to learn more.

Click here to register for VBA Training Online

VBA Training Online Press Release

Excel Class Free VBA Tutorial – Variables

Few things in life are constant. Heraclitus said: The only thing constant is change. Change is constant and variables are used to capture change. In the context of Excel, the names of workbooks change, the number of workbooks you have open is variable, the number of sheets, pivot tables and charts is usually variable, the name of sheets is variable, the values in cells are variable, and on, and on.

Continue reading

Excel VBA Tutorial: Learn To Use VBA To Overcome Macro Recorder Limitations

Reposted from
http://www.vbaclass.com/blog/excel-vba-tutorial-learn-to-use-vba-to-overcome-macro-recorder-limitations/

If you record a macro where you sort your data, the macro records the last contiguous row in the data set. Next time you run the macro, if you have more rows of data the recorded macro wont correctly sort to the new last row. Also, anytime you record a macro to sort data, the macro records the name of the sheet where you sorted the data. If your data appears on a sheet with a different name, the macro won’t work.

In our previous blog tutorial I instructed how to dim a variable to capture change (see http://www.vbaclass.com/blog/excel-vba-class-free-vba-tutorial-variables/).

In this lesson I will explain how to use Excel Visual Basic Applications to find the last row of data and concatenate a variable into a recorded macro.

To follow along, download this sample workbook.

Many VBA blogs recommend what I consider an ineffective method of locating the last row of data. Those blogs recommend you use the Goto > Special > Last Cell command.

Goto Command (Keyboard shortcut is Ctrl+G)

Excel Goto Command

Goto Dialog – Special Button

Excel Goto Special

Goto Special Dialog

Goto Special Last Cell

In VBA, the code to achieve that command is

Selection.SpecialCells(xlCellTypeLastCell).Select

However theres a potential problem with using that command. If you have deleted rows or columns, this brings you to the end of the deleted data, not the active data. Once data is placed in a cell the cells status changes from clean to dirty. The Goto > Special > Last Cell command may bring you past the end of the actual data.

If you try Goto > Special > Last cell on the Sheet1 worksheet tab in the workbook you downloaded (link) notice you end up in an empty cell. There used to be data there, but it was deleted. The Goto > Special > Last Cell command isn’t aware of this condition.

A more reliable method is, assuming you know that you have data starting in column A, go to the bottom of the spreadsheet, like cell A1000000. From cell A1000000 press ctrl+up arrow key to jump to the last row of data.

In VBA I would code this as follows:

Range(“A1000000”).Select
Activecell.end(xlup).select
Dim lastrow
Lastrow=activecell.row

In this code, the lastrow variable stores the row of the activecell, the last cell in column A that contains a value.

Now that youve located the last row you can concatenate your lastrow variable into any Excel macro.

In Excel, the concatenation operator is the & symbol. You could use the & concatenation operator in a formula as follows:

Concatenate Operator

In VBA the concatenation operator works in a similar fashion with variables.

In the workbook you’ve downloaded previously (link) Sheet1 has 86 rows of data, while Sheet2 has 89 rows of data. I recorded a macro to sort the data on Sheet1 by State (column C). Here is the code generated by sorting the data.

Sub Macro1()
Range(“C3”).Select ‘I right clicked on cell C3 to access the sort command
‘*******************All of this code is a sort command in VBA************* ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear ‘Problem with the sheet name
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Range(“A1:H86”) ‘Here is where the macro recorder hard-codes row 86
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
‘******************End of the Data Sort command***************************
End Sub

Now, if you try to run the macro on Sheet2, it fails for two reasons. First, the recorded macro hard-codes the name of the sheet three times. Even if we fix the sheet name issue, it will still only sort to row 86, but Sheet2 has 89 rows of data.

To fix this problem we need to substitute the activesheet object for the code that reads Worksheets(“Sheet1”). In the updated code shown below, i have commented out the bad lines and marked where i used the activesheet object. The second issue is the last row as Range(“A1:H86”), that’ where i’ll concatenate the lastrow variable. The edited macro appears below

Sub Macro2()

‘*************Add Code To Find The Last Row*************
Range(“A1000000”).Select
ActiveCell.End(xlUp).Select
Dim Lastrow
Lastrow = ActiveCell.Row
‘*************End of Code To Find The Last Row*************

Range(“C3”).Select

‘*************Commented Out Recorded Macro Code*************
‘ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear
‘ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
‘With ActiveWorkbook.Worksheets(“Sheet1”).Sort

‘*************Code using Activesheet Instead*************
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort

‘*************Concatenate Last Row Here*************
‘.SetRange Range(“A1:H86”) ‘I commented out the recorded macro code here
.SetRange Range(“A1:H” & Lastrow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

I hope you will find this free Microsoft Excel VBA Tutorial useful. If you have questions or comments please contact me for assistance. If you have other topics you recommend I should blog about with Excel VBA please feel free to make suggestions.

To contact Excel Class Training about Excel VBA Classes visit our contact page here