Excel Templates - Excel Training - Excel Add-ins

April Ozgrid Excel Newsletter**For Free 24/7 Microsoft Office® Support See:****Our Free** Excel Help ** & VBA Help Forum**.**See Also our:** Free Excel Newsletter

Complete Excel Excel Training Course. Instant Buy/Download, |

Recently Added Software | Excel Tips and Tricks | Excel VBA Tips and Tricks | Main Software Categories |Newsletter Archives Recently Added SoftwareCOM Add-in Developer

Trading Analyser Software

Technical Indicators

Interactive Charts

Excel Fund System

Excel Spread Trading

Universal Pricing Calculations

Mathematical Functions

Linear Algebra

Ordinary Differential Equations

Invoice Software

Inventory Software

For this months Excel tips and tricks we will look at one of the most popular asked questions. That is, how to count using multiple criteria and how to sum using multiple criteria.

***Count/Sum With Multiple Criteria***

They are quite a few ways this can be achieved, but some ways are more efficient than others. Let's Start with the least efficient, but the easiest to do - array formulas.**What are Array Formulas?**

Excel Array formulas are very powerful and useful formulas that allow more complex calculations than standard formulas. The "Help" in Excel defines them as below:

"An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments."

Before we show some examples of array formulas it is **important to know four fundamental rules.**

1) Each argument within an array must have the same amount of rows and/or columns.

2) You must enter an array by pushing **Ctrl**+**Shift**+**Enter**. This will add the curly brackets around the formula.

3) You cannot add the {} (braces) that surround an array yourself, pushing **Ctrl**+**Shift**+**Enter** will do this for you.

4) You cannot use an array formula on an entire column.

For the purpose of the examples I will base all of them on the data being in columns **A:D** down to row 100, with row 1 of all columns being a heading. Column A will be **Names**, B will be **Age**, C will be **Male/Female** (M/F), D will be **Wage**.**Count of; Name=Dave, Age>20 M/F=M, Wage>500**

=SUM(((A2:A100="Dave")*(B2:B100>20)*(C2:C100="M")*(D2:D100>500)))**Count of; Age between 21 and 29, Wage between 301 and 399**

=SUM((B2:B100>20)*(B2:B100<30)*(D2:D100>300)*(D2:D100<400))

Although these examples use the SUM function the results are counted and not summed. This is because the SUM function sums the results of the multiplication of TRUE (has a value of 1) and FALSE (has a value of 0). So, any row that has a FALSE value will result in the value of zero. As including zero in the multiplication always yields a result of zero, one FALSE means a result of zero. If all are TRUE, then it ends up being SUM(1*1*1) which of course always equals 1.

The other way, which is more efficient, is by using the **SUMPRODUCT** function. It too uses the same principle as described above for arrays. However, one DOES NOT need to enter the SUMPRODUCT via **Ctrl**+**Shift**+**Enter****Count of; Name=Dave, Age>20 M/F=M, Wage>500**

=SUMPRODUCT((A2:A100="Dave")*(B2:B100>20)*(C2:C100="M")*(D2:D100>500))**Count of; Age between 21 and 29, Wage between 301 and 399**=SUMPRODUCT((B2:B100>20)*(B2:B100<30)*(D2:D100>300)*(D2:D100<400))

Now, to use the same data to

=SUMPRODUCT((A2:A100="Dave")*(B2:B100>20)*(C2:C100="M")*(D2:D100>200)*(D2:D100<400)*(D2:D100))

Again, the use of the

If you are going to be counting, or summing a large number of cells I would advise strongly to use the appropriate Dfunction (Database Function). These are designed specifically for the job of using multiple criteria. The Excel help has lots of information and details on these functions, just search for "Database Functions".

As we did last month, I'm going to include what appears to be the most popular selections of VBA codes from our site, please enjoy and don't forget to use our free question and answer forum for Excel Help .

This UDF was written by myself to take the place of **VLOOKUP** when you need to look across ALL the Worksheets in the active Workbook. As with the Standard VLOOKUP, it stops at the first match.

**Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _Col_num as Integer, Optional Range_look as Boolean)**

''''''''''''''''''''''''''''''''''''''''''''''''

'Written by OzGrid.com

'Use VLOOKUP to Look across ALL Worksheets and stops _

at the first match found.

'''''''''''''''''''''''''''''''''''''''''''''''''

Dim wSheet As Worksheet

Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets

With wSheet

Set Tble_Array = .Range(Tble_Array.Address)

vFound = WorksheetFunction.VLookup _

(Look_Value, Tble_Array, _

Col_num, Range_look)

End With

If Not IsEmpty(vFound) Then Exit For

Next wSheet

Set Tble_Array = Nothing

VLOOKAllSheets = vFound

To use this code, do this:

1. Push **Alt**+**F11** and go to **Insert**>**Module**

2. Copy and paste in the code.

3. Push **Alt**+**Q** and Save.

Now in any cell put in the Function like this: **=VLOOKAllSheets("Dog",C1:E20,2,FALSE) **

Where "Dog" is the value to find.

C1:E20 is the range to look in the first column and find "Dog".

2 is the relative column position in C1:E20 to return return our result from.

FALSE (or ommited) means find and exact match of "Dog".

In other words the UDF has the exact same syntax as Excels VLOOKUP. The only difference is that it will look in ALL Worksheets and stop at the first match. You can find the UDF (VLOOKAllSheets) in the Paste Function dialog (

============================================================================

One very common problem encountered by most Excel users is the correct entering of Excel dates. When a date is not entered in the correct format, Excel will not recognize it as a valid date. This often means you cannot perform calculations with the 'so-called' dates. It also means any charts, Pivot tables etc based off the dates will not be valid. While the use of Excels Validation can help with this it is far from bullet proof. Below is a quite simple way we can have all our dates entered in Excel correctly. Just follow the steps and you will be up and running in 10 minutes. Download Demo , Or Download Advanced Demo , where you can add x days, months and years to any date.

1)Open the workbook for the calendar. It is a good idea to use your

2)Go to

3)Go to

4)Right click on the Toolbox and select

5)Scroll through the list until you see:

6)Now click the Calendar that is now part of the Toolbox and then click on the UserForm we inserted in step 3.

7)Use the size handles on both the UserForm and the Calendar Control to make them both a reasonable size.

8)Now ensure the UserForm is selected (as shown above) then go to

9)Select

10)Now go to

**Private Sub Calendar1_Click()**

ActiveCell = Calendar1.Value

ActiveCell.NumberFormat="mm/dd/yy"**End Sub**

11)Now go to **Insert**>**Module** and in this Public Module place this code:

**Sub ShowIt()**

UserForm1.Show**End Sub**

12)Ok, nearly done. Click the top right** X** (or push **Alt**+**F11**) to return back to Excel.

13)Go to **Tools**>**Macro**>**Macros** (**Alt**+**F8**) and then select * ShowIt* click

Download Demo

Download Advanced Demo

where you can add x days, months and years to any date.

__Until next month, keep Excelling!__

**Instant Download and Money Back Guarantee on Most Software**

**Excel Trader Package**Technical Analysis in Excel With $139.00 of **FREE software!**

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft