Excel Class New Excel Blog

Continue reading## 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 – 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

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:

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

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)

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

Today 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

Many 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:

- 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;
- In any Excel workbook: Hold the Alt key and tap the F11 function key to open the VB Editor.
- Select the Insert menu > Module command.
- Paste the copied code into the empty white code window.
- To run the program, close the VB Editor and switch back to Excel.
- Select the View tab of the ribbon, then click the Macros button to the far right (or choose the View Macros command).
- 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

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;

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;

4. To the right and below the formula, add variable inputs;

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.

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)

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.

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

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

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.

## 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 Class NYC Custom Table Styles Free Tutorial

Excel Classes in NYC offers a free Excel tutorial on how to create a custom table style. To learn Excel in NYC, call us at 212.828.7089 to discuss your Excel training objectives.

Continue reading## Excel VBA Tutorial: Learn To Use VBA To Overcome Macro Recorder Limitations

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)

### Goto Dialog – Special Button

### Goto Special Dialog

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:

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