Monthly Archives: July 2022

Excel

Consolidation

You have set of reports, the structure is not identical throughout the reports. Let’s see what Excel consolidation can do.

Icon for consolidation can be found under data menu, and top right-hand corner in data tools section.

We have sales reports from three regions North, South and East. Each of region consists of four sales representatives who are selling trucks, vans and automobiles. The reports have two dimensions.

Report from South.

Report from North.

Report from East.

As you noticed, the files have been named according to the geographical region.

Sales rep Jeremy works only in East region replacing Jack. He works in South and North.

Open the consolidation Excel sheet and press the consolidation icon.

The consolidate box appears. I placed the cursor in B2 cell in each of the reports.

The function is SUM as we are summing up all the three regions.

I activate all the three parameters under “Use labels in”.

I pressed the arrow up at the end of the reference window and consolidate -reference window opens.

Just open the first report and activate the data range. Press enter.

Press add.

The reference is moved to all references window.

Empty reference window and press the arrow at the right-hand side of the window.

Browse the data range in the next report and press enter.

Again, press add.

Empty the reference window and press the arrow up.

Browse the data range from the last report and press enter.

Add the last data range into the all references window.

Empty the reference window and press ok.

This report appeared in the consolidation Excel. The main window includes sales reps and product categories. The regions are not visible.

If you press number two in left-hand side margin. Then the region-specific division appears. Note that the product line, like trucks, is the sum line. For example, Jill was selling three trucks in all the three regions.  The Excel correctly calculates the different sales reps. 

Consolidation might be handy sometimes, if you combine data tables which are not totally equal. A benefit with consolidation in Excel, is that it is easy to take into use. It does not take long time to comprehend the feature.

Uncategorized

Automated SUM

You have a range of cells with values. Then you want to create sum function at the bottom and on the right hand side of the range.

The data range here is B2:E5.

Sum functions are needed in row 6 and column F. This is easy and can be done manually, of course. Just copy the sum to remaining rows and columns.

If you need to do this process repeatedly, a macro might help you. The VBA is added at the end of this blog.

To run the macro, you need to place the cursor in the top left corner of the data range. In our case, the start cell is B2.

Zs is the address of the start cell, eg B2. Then we go at the end of the B row, the address of that cell is stored in zl. Then we go one line down and add the sum sentence SUM(start cell:end cell), then we go one row upwards and one column to right. If that cell is empty then we need to add the sum sentences for each row. If that cell is not empty then we continue counting column sums.

After counting column sums, then we go to the start cell, in this case B2. Zs and zl are populated again. We move to the right end of the data range. Then we move one step to right and there the SUM(start cell:end cell) sentence will be created. Then we move one row down and one column to left. If the cell is empty, then the macro is finished. If the cell is not empty, then we continue counting row sums.

Instead of manually creating sum sentences, place the cursor in B2 and execute the VBA below.

Sub z_sum2()

Dim zs, zl, zss

zss = ActiveCell.Address

Do

zs = ActiveCell.Address

Selection.End(xlDown).Select

zl = ActiveCell.Address

ActiveCell.Offset(1, 0).Select

ActiveCell.Formula = “=sum(” & zs & “:” & zl & “)”

ActiveCell.Offset(-1, 1).Select

If ActiveCell.Value = “” Then

    GoTo zrow

Else

    Selection.End(xlUp).Select

End If

Loop

zrow:

Range(zss).Select

Do

zs = ActiveCell.Address

Selection.End(xlToRight).Select

zl = ActiveCell.Address

ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = “=sum(” & zs & “:” & zl & “)”

ActiveCell.Offset(1, -1).Select

If ActiveCell.Value = “” Then

    Range(zss).Select

    MsgBox “Done.”

    End

Else

    Selection.End(xlToLeft).Select

End If

Loop

End Sub

By the way, if you copy the VBA code from website and paste it into Excel VBA editor, and still editor shows syntax errors. When copy pasting the code from application to another, extra spaces might appear in the front of sentence. If something does not work, try to remove tabs or spaces in front of the sentence. Another option is to rewrite the line. Also if you copy the code from website, paste the code first into Notepad. Then copy from Notepad and paste into Excel VBA editor. This might help also.

Before publishing anything, I have tested that VBA works with my Excel.

Excel, Functions

SUMIF with asterisk

You have data with date format DD-MMM-YYYY like 24-Jun-2022. You need to create a report to sum the sales data per month.

Here is a sample data. The report is needed about sales volume per month.

We can use SUMIF function. We need three arguments:

  1. Range: the column for dates, where the months are found.
  2. Criteria: which month we are examining.
  3. Sum range: where are the values to be calculated.

As months are abbreviated in column A, and the months are in the middle of string, we need to find the January asterisk Jan asterisk.

The sentence is here:

=SUMIF($A$3:$A$42;”*jan*”;$B$3:$B$42)

Another case is that month is a value in a cell not inside the function argument.

The Excel understands the second argument as * Jan *. Therefore we need to have asterisks in quotation marks. The dynamic cell reference needs to be separated with ampersands from the static asterisks.

=SUMIF($A$3:$A$42;”*” & E4 & “*”;$B$3:$B$42)