Instructions

Select all values and the blank total cells.

Press F5 or go to tab "Home" on the ribbon, click on "Find & Select" button and then click on "Go To.."

Click on "Special..." button.

Click on "Blanks" and then on "OK" button.

Go to tab "Home" on the ribbon and click on "AutoSum" button.

All empty cells now have a SUM formula that adds all above values to the next SUM formula.

Select cell C18 and type this formula:

Press Enter. Copy cell C18 and paste to cell range D18:F18.

Explaining formula in cell C18

ISFORMULA(C3:C17) checks if a cell in cell range C3:C17 has a formula. It returns TRUE or FALSE.

ISFORMULA(C3:C17) returns this array: {FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}

The picture below shows this array in column D.

The array shows that there is a formula in C5, C9, C12 and C17.

ISFORMULA(C3:C17)*C3:C17 multiplies the boolean values with their corresponding values in column C, shown in column E below.

The SUMPRODUCT function then sums all values in the array. Why not use the SUM function? You need to enter it as an array formula if you use the SUM function.

Download excel *.xlsx file

Find empty cells and sum cells above.xlsx

The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired output from a database or copying values from an HTML file.

The INDEX function allows you to easily rearrange values on an Excel worksheet, in this case, data seems to be grouped record by record. 4 values in one record.

Column D to G shows you the INDEX function rearranging the data.

How to build the formula

The INDEX function has three arguments: INDEX(reference, row_num, [column_num])

The reference in the first argument points to cell range B2:B17. To return a value from that cell range you must know where it is, the INDEX function uses a row and column number to locate a particular value. Since this cell range (B2:B17) only has one column you only need to use a row number to get the value you want.

The picture above has relative row numbers in column A to show you what number the INDEX function needs for it to return a specific value from cell range B2:B17. The values in column B seems to repeat, every 4th row has a company name. We can use that information to build the formula that returns a record in a row each.

The formula needs numbers in a determined sequence depending where on the worksheet it is. If I enter the formula in cell D2 it must get values in the order shown in the picture below.

Combining the ROWS function and the COLUMNS function lets you build the number sequence shown above.

Formula in cell D2:

COLUMNS($A$1:A1) returns 1 and ROWS($A$1:A1)*4-4 returns 0 (zero). 1 +0 is 1. The INDEX function returns the first value in cell range B2:B17 which is "Company".

In cell E2 COLUMNS($A$1:A2) returns 2 and ROWS($A$1:A1)*4-4 returns 0 (zero). 2+0 is 2. The INDEX function returns the second value in cell range B2:B17 which is "Item".

In cell D3 COLUMNS($A$1:A1) returns 1 and ROWS($A$1:A2)*4-4 returns 4. 1+4 is 5. The INDEX function returns the fifth value in cell range B2:B17 which is "Vegetables are us".

Download excel *.xlsx file

Rearrange values.xlsx

This article explains how to set up a workbook so a macro is executed every time you open the workbook. Excel event code makes this possible and it is easier than you think.

For example, you could have a macro that opens another workbook that you need, saving you time, making you happy and feeling awesome.

Create a macro

The following macro opens workbook Book1.xlsx in folder c:\temp

Sub Macro1
Workbooks.Open ("c:\temp\Book1.xlsx")
End Sub

Copy the macro code above and go to tab "Developer" on the ribbon. If it is missing search the internet for your Excel version and "Show developer tab".

Click on "Visual Basic" button to open the Visual Basic editor. Right-click on your workbook in the Project Explorer window.

Click on "Insert" and then on "Module". This action adds a code module to your workbook.

Now paste the VBA code above to your code module.

Go back to Excel. Go to tab "Developer" and click on "Macro" button. The following dialog box appears:

Click on "Run" button and the workbook is opened.

The following article shows you how to open multiple excel files using checkboxes:

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The […]

Comments(38) Filed in category: Check-boxes, Excel, Files and folders

How to run a Macro upon opening a workbook

Press Alt+F11 to open the VB Editor or go to tab "Developer" on the ribbon, click on "Visual Basic" button. Double click on "This Workbook", if you can't see it expand the list by clicking on the + sign.

Paste the following event code to the workbook module:

Private Sub Workbook_Open()
Macro1
End Sub

Go back to Excel. Save the workbook with file extension *.xlsm, this is important.

Close workbook and open it again. The workbook Book1.xlsx in folder c:\temp is now automatically opened. (If it exists..)

My definition of unique values are values that exist only once in a cell range. The image below shows you a list in column B, some of these values have duplicates.

The list in column D contains only values that are unique, Aa and BB exists only once in the list, all other values have a duplicate.

The following article demonstartes how to construct a formula that counts unique distinct values based on a condition.

The image below shows a table in column B and C. Column B contains names and column C contains products. How many unique distinct products did Salesperson Jennifer sell?