Excel Digest

The SUMIF function is a direct way to sum values based on a single criteria. If we want to sum or add  values based on multiple criteria  however, then we need to take extra steps. One alternative for this is to use the SUM function and the IF function.

In the example below we want to sum up the total sales for “John” in quarter 2 (Q2). i.e. our criteria is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:

=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))

However this formula should be an “Array formula”. To make it an “Array formula” you should press CTRL+SHIFT+ENTER while you are in the edit mode of the formula. It should then look like this :

{=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))}

This formula will give us a result of 9547 (summing sales in the yellow rows).

Note:

The formula presented in this post is generated using the conditional sum wizard. You can try the wizard for yourself.

If you have the Analysis Toolpak installed, then the wizard should be available in the Tools menu. If not then read more on: How-to-install-and-load-the-analysis-toolpak.

To calculate the number of months between two dates, there are two possibilities:

1. Both dates occur in the same year.
2. Dates occur in two different years.

Case 1 - both dates occur in the same year

Use the MONTH function to extract the month from each date, then subtract the two months from each other.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/09/2007 in cell A1 and the date 31/12/2007 in cell B1 (both cells formatted as date). To calculate the number of months between these two dates you use the following formula:

=MONTH(B1) - MONTH(A1)

This should give a result of 3.

Note that the cell where you write the formula must be formatted as number and not date.

Case 2 - dates occur in two different years

Use the the YEAR function and the MONTH function.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/09/2007 in cell A1 and the date 31/03/2008 in cell B1 (both cells formatted as date). To calculate the number of months between these two dates you use the following formula:

=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)

This should give a result of 6.

Again the cell where you write the formula must be formatted as number and not date.

To calculate the difference or the number of days between two dates, you simply subtract one date from the other.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/11/2007 in cell A1 and the date 15/01/2008 in cell B1 (both cells formatted as date). To calculate the difference between these two dates you use the following formula:

=B1 - A1

This should give a result of 51 ( 5 days in Nov + 31 in Dec + 15 in Jan).

Note that the cell where you write the formula must be formatted as number and not date.

To sum values based on a specified criteria or condition you use the SUMIF function, and its syntax is as follows:

SUMIF(range,criteria,sum_range)

Range is the range of cells that you want to be evaluated using the specified criteria.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added or summed.

Sum_range are the actual cells to add if their corresponding cells in the range match criteria.

In the following example if we want to sum all the sales for Product3 then we will use the following formula:

=SUMIF(A2:A13;”Product3″;C2:C13)

I.e. we will search for “Product3″ in the range A2:A13. When it is found then the corresponding value (in the same row) from the range C2:C13 will be added to the sum. 

This will give a result of 465 (77 + 200 + 188).

If you have a workbook with a considerable number of worksheets, some visible and other hidden, and you want to print only the visible sheets, then you can use this macro:

Sub PrintVisibleSheets()
‘***************************************************
‘Print only visible sheets in the active workbook *
‘***************************************************
Dim wSheet As Worksheet
For Each wSheet In ActiveWorkbook.Worksheets
If wSheet.Visible Then wSheet.PrintOut
Next
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Check this post if you want to know How to write a VBA macro.

One of the options in the Data menu is the Subtotals. It allows you to create subtotals for a specific column in your worksheet, based on a key in another column.  The subtotals will be added to the original data which will be retained as it is.

The best way to explain how to create a subtotal is by using an example.

In the image below you see a list of products, salesmen and total sales for a product / salesman.

To create subtotals of the “Total Sales” by product, you perform the following steps:

1. Prepare your data table, make sure you have columns headings and sort the data by your key or category (Product in our example).

2. Select the data range or simply click on a cell within the range.

3. In the Data menu click Subtotals. The Subtotal dialogue will open.

4. From the combo box labeled “At each change in:” select the category on which you want to base the subtotals.

5. From the combo box labeled “Use function:” select the function to be used in calculating the subtotals. Usually you will use the SUM function, but in some cases you may just want to know “how many items” so you will use the COUNT function instead.

6. From the list labeled “Add subtotal to:” select the column to be summarized into subtotals.

7. Click OK.

8. The new list of your data will look like this.

9. When the subtotals are created, Excel also defines groups based on the rows used to calculate the subtotals. The groupings form an outline of your worksheet based on the criteria that you used to create the subtotals.

At the top left corner of the image above you will see the numbers 1 2 3. This is the outline of your subtotals.

• Click on 3 and you will get all details of your data.
• Click on 2 and you will only get the subtotals by category.
• Click on 1 and you will only get the grand total.

This is what you get when you click “2″.

Note:

To remove subtotals:

1. In the Data menu click Subtotals.

2. When the Subtotal dialogue opens, click the button labeled “Remove All”.

To print all worksheets in a workbook then :

1. In the File menu click Print.

2. The Print window will open.

3. At the bottom of the Print window find a section labeled: Print what .

4. Click on the radio button labeled: Entire workbook.

5. Click OK.

Applies to Excel 2003

To copy or move worksheets within the same workbook follow these steps:

1. Switch to the workbook which has the sheet to be copied or moved.

2. in the Edit menu click Move or copy sheet.

3. The Move or copy dialogue will open.

4. The To book drop down will default to the current workbook. Leave this as it is.

5. in the Before sheet list click where you want the sheet to be moved (position within the workbook).

6. Select the Create a copy check box if you want to retain the original worksheet , or deselect it if you just want to move the worksheet to a new location withinthe workbook.

Applies to Excel 2003

If you know Visual Basic programming then you can create your own VBA macro and run from within Excel.

Follow the steps below:

1. On the Tools menu select Macros.

2. On the Macro submenu select Visual Basic Editor.

3. You will be switched to Visual Basic Editor Menu.

4. On the Insert menu (Visual Basic Editor) select Module.

5. This will open the code window.

6. Type the Visual Basic code for your macro.

Here is a simple example. A macro to increment the variable i by 5 and write it to column 2 (Column B) in the next row :

Sub TestMacro()
Dim i As Integer, Row As Integer, Col As Integer
i = 0
Col = 2
For Row = 1 To 10
i = i + 5
ActiveSheet.Cells(Row, Col).Value = i
Next Row
End Sub

7. On the File menu click Close and return to Microsoft Excel.

8. Your new macro is now ready to be run.

To run your macro:

1. On the Tools menu select Macros. A list of available macros will be displayed.

2. Select your macro and click Run.

Applies to: Excel 2003

To print more than one worksheet in a workbook then :

1. Select the worksheets you want to print.

1. If the sheets to be printed are adjacent then click on the first one, press SHIFT then click the last one.
2. If they are not then click on the first one, press CTRL then click the others one at a time.

2. In the File menu click Print.

3. The Print window will open.

4. At the bottom of the Print window find a section labeled: Print what .

5. Click on the radio button labeled: Active sheet(s) .

6. Click OK.

Applies to Excel 2003