Multiplies corresponding components in the given arrays, and returns the sum of those products.

What does that mean? I´ll demonstrate with a few examples.

Example 1 - The basics

Formula in cell B7:

Step 1  - Multiplying corresponding components in the given arrays

The first array is in cell range B2:B4 and the second array is in cell range C2:C4.

B2:B4*C2:C4

becomes

{1;2;3} * {4;5;6}

becomes

{1*4; 2*5; 3*6}

and returns {4; 10; 18}. The same calculations are done in column D and shown in column E, see above picture.

Step 2 - Return the sum of those products

{4; 10; 18}

becomes

4 + 10 + 18

and returns 32 in cell B7. The same calculation is done E5, the sum of the products in cell range E2:E4 is calculated in cell E5. See above picture.

Now you know the basics. Let´s move on to something more interesting!

Example 2 - Use a logical expression

Formula in cell G4:

Step 1  - Multiplying corresponding components in the given arrays

There is only one array in this formula but something else is distorting the picture. A comparison operator (equal sign) and a second cell value (G2) or a comparison value. With these we have now built a logical expression. This means that the value in cell G2 is compared to all the values in cell range B2:B6 (not case sensitive).

B2:B6=$G$2

becomes

{"Alaska";"California";"Arizona";"California";"Colorado"}="California"

and returns

{FALSE;TRUE;FALSE;TRUE;FALSE}.

They are all boolean values and excel can´t sum these values. We have to convert the values to numerical values. There are a few options, you can:

• Add a zero - (B2:B6=$G$2)+0
• Multiply with 1 - (B2:B6=$G$2)*1
• Double negative signs  --(B2:B6=$G$2)

They all convert boolean values to numerical values.

--( {FALSE;TRUE;FALSE;TRUE;FALSE})

becomes

{0;1;0;1;0}

Step 2 - Return the sum of those products

{0;1;0;1;0}

becomes

0 + 1 + 0 + 1 + 0

and returns 2 in cell G4. There are two cells containing the value "California" in cell range B2:B6. You accomplish the same thing using the countif function or count multiple values in different columns using the countifs function. In fact, you can count entire records in a table.

Example 3 - Use multiple logical expressions

Formula in cell D10:

This formula contains three arrays, you can use up to 30 arrays. You can make the formula somewhat shorter:

This allows you to have a lot more expressions, if you like. It is only the available computer memory that is the limit. The formula looks like an array formula but don´t be fooled, you are not required to enter it as an array formula.

Step 1  - Multiplying corresponding components in the given arrays

(B2:B8=B10)*(C2:C8=C10)*D2:D8

becomes

({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})*({FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE})*{10; 20; 40; 10; 20; 30; 10}

becomes

{0;0;0;1;0;1;0}*{10; 20; 40; 10; 20; 30; 10}

and returns

{0; 0; 0; 10; 0; 30; 0}

Step 2 - Return the sum of those products

{0; 0; 0; 10; 0; 30; 0}

becomes

10 +30

and returns 40 in cell D10.

Example 4 - Use multiple logical expressions and mathematical operators

Mathematical operators between arrays allows you to do more complicated calculations, like this:

Formula in cell D10:

These expressions check if California is found in cell range B2:B8 or Las Vegas is found in cell range C2:C8. They are found in row 3, 5,7 and 8. The sumproduct function sums the corresponding values in column D and returns 70 in cell D10. 20+10+30+10 equals 70.

Download excel *.xlsx file

Sumproduct function.xlsx 

Hi Oscar,
I have the following issues. Any suggestions.

Sheet 1 :
Grade Male Female
Primary
Secondary
High School
College
Gender

Sheet 2 :
Col A : Student Id
Col B: Gender : Here we specify whether the student is male or female
Col C : Here we specify category to which the student falls : Primary, secondary, high school, college
Col D : Age : To which age they are in

Problem :

In Sheet 1,
# I should give the count of students who are male and are in
* primary and they are between 4 to 6 years.
* Count of Secondary students and between 6 to 10 years ...

# I should give the count of students who are female and in
* primary and they are between 4 to 6 years.
* Count of Secondary students and between 6 to 10 years

Kindly let me know of any specific formula e that I can use.

Answer

Sheet 2 - Example data

Sheet 1 - Count students

Formula in cell B2:

You can copy this formula to cell range B2:C5. The formula takes advantage of both absolute and relative cell references . For this to work, copy the cell, not the formula in the cell or formula bar.

Example,

1. Select cell B2
2. Copy cell B2 (Ctrl + c)
3. Select cell range B2:C5
4. Paste (Ctrl + v)

Read more about absolute and relative cell references

Formula in cell B6:

If you are familiar with array formulas, use this array formula in cell B6:

The formula is smaller but needs to be entered as an array formula.

How to enter an array formula

1. Select cell B6
2. Paste formula to formula bar
   
3. Press and hold CTRL + SHIFT
4. Press Enter simultaneously
5. Release all keys

If you did it right, the formula now begins and ends with curly brackets, like this:

Explaining formula in cell B2

Step 1 - Compare value in cell A2 (sheet1) to values in cell range C2:C169 (sheet2)

$A2=Sheet2!$C$2:$C$169

becomes

"Primary"={"College"; "College"; "High school"; ... ; "Secondary"}

and returns

{FALSE; FALSE; FALSE; ... ; FALSE}

Step 2 - Compare value in cell B1 (sheet1) to values in cell range B2:B169 (sheet2)

B$1=Sheet2!$B$2:$B$169

becomes

"Male"={"Male"; "Female"; "Male"; ... ; "Female"}

and returns

{"TRUE"; "FALSE"; "TRUE"; ... ; "FALSE"}

Step 3 - Multiplying arrays

($A2=Sheet2!$C$2:$C$169)*(B$1=Sheet2!$B$2:$B$169)

becomes

("Primary"={"College"; "College"; "High school"; ... ; "Secondary"})*("Male"={"Male"; "Female"; "Male"; ... ; "Female"})

becomes

({FALSE; FALSE; FALSE; ... ; FALSE})*({"TRUE"; "FALSE"; "TRUE"; ... ; "FALSE"})

and returns

({0; 0; 0; ... ; 0})

Step 4 - Sum all values in the array

=SUMPRODUCT(($A2=Sheet2!$C$2:$C$169)*(B$1=Sheet2!$B$2:$B$169))

becomes

=SUMPRODUCT({0; 0; 0; ... ; 0})

and returns 5 in cell B2.

Explaining formula in cell B6

Step 1 - Compare value in cell B1 (sheet1) to values in cell range B2:B169 (sheet2)

B$1=Sheet2!$B$2:$B$169

becomes

"Male"={"Male"; "Female"; "Male"; .. ; "Female"}

and returns

{"TRUE"; "FALSE"; "TRUE"; .. ; "FALSE"}

Step 2 - Convert boolean values to numerical values

SUMPRODUCT function (and the sum function) can´t sum boolean values (TRUE, FALSE). Multiplying with 1 converts TRUE to 1 and FALSE to 0.

(B$1=Sheet2!$B$2:$B$169)*1

becomes

{"TRUE"; "FALSE"; "TRUE"; .. ; "FALSE"}*1

and returns

{1; 0; 1; .. ; 0}

Step 3 - Sum values

SUMPRODUCT((B$1=Sheet2!$B$2:$B$169)*1)

becomes

SUMPRODUCT({1; 0; 1; .. ; 0})

and returns 71 in cell B6.

More to note

You can also use the SUMIF and SUMIFS function to count values. See this post:
Sum values between two dates with criteria

Download excel *.xlsx file

Count students.xlsx

Functions in this post

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

SUM(number1,[number2],)
Adds all the numbers in a range of cells

Step 1 - Insert a scatter chart

1. Go to "Insert" tab on the ribbon
2. Click "Scatter" button
3. Click "Scatter with only markers" button

Step 2 - Create a drop down list

1. Select cell B14
2. Go to tab "Data" on the ribbon
3. Click "Data Validation" button
4. Go to "Settings" tab
5. Select "List"
6. Select source range: B3:B10
7. Click OK

Formula in cell C14:

Formula in cell D14:

Step 3 - Select chart data source

1. Right click on chart
2. Click "Select Data"
3. Click "Add" button
   
4. Select name, x value and y value.
   
5. Click Ok.

Step 4 - Insert a background picture

1. Right click on chart
2. Click "Format Plot Area..."
3. Click "Fill"
4. Select "Picture or texture fill"
5. Click "File..." button
6. Select a picture
7. Click "Insert"
8. If you like, change "Transparency" value
9. Click "Close"

Step 5 - Chart settings

1. Delete Legend and chart titel
   
   
2. Delete chart grid lines
   
3. Change x and y axis minimum and maximum value to 0 and 100
   
4. Make sure x and y coordinates in the table are ok
   
5. If you like, delete x and y axis
   
6. Select data series on the chart
   
7. Right click on data series
8. Click "Format Data Series..."
   
9. Click "Marker Options"
10. Select "Built-in"
11. Select a type
12. Click "Marker Fill"
13. Select "Solid fill"
14. Pick a color
15. Click Close

Final notes

First I thought of using longitude and latitude coordinates but I gave that up really quickly. The map is downloaded from Wikimedia Commons.

Tip! You can add a data label and use the series name to show the city name on the map.

Download excel *.xlsx file

Maps in a xy scatter chart.xlsx

Functions in this post

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

SEARCH(find_text,within_text, [start_num])

Arguments

find_text - Is the text you want to find. ? and * wildcard characters are allowed.

within_text - is the text in which you want to search for find_text

start_num - is the character number in within_text, counting from the left at which you want to start searching. Optional, if omitted 1 is used.

Formula in cell C5:

The function returns 11 in cell C5. Text string "blue" is found and begins in the 11-th character in "My car is blue". See cell range F5:S6.

I am using a absolute cell reference to cell D5 so I can copy the function to cell C11 without changing the argument. If you are interested in how absolute and relative cell references work, read this post:  Absolute and relative cell references

Formula in cell C11:

becomes

=SEARCH("blue","My car is white")

and returns #VALUE!. Text string "blue" is not found in "My car is white".

FIND function
The function works the same as the search function except case-sensitive.

SEARCH function returns an array

Array formula in cell B5:B9: 

becomes

=SEARCH("is", {"My car is blue"; "My car is white"; "My house is grey"; "My bird was green"; "My dog is here"})

and returns {8; 8; 10; #VALUE!; 8} in cell range B5:B9. I have highlighted the beginning character in cell range D5:T9.

Download excel *.xlsx file

Search and Find functions.xlsx

The first sheet contains an overview. You have the option to select a year, date and color. Days with many "events" have a darker color, days with less "events" have a lighter color.

The window to the right shows all events for the selected day. Double clicking on a month takes you to sheet "Month". Double clicking on a date takes you to sheet "Day".

Sheet Month

(Click to view a larger image)

You can quickly jump between months and years using the top two rows. The first four "events" are shown in each date. Select a date and all events are displayed in the window to the right.

Double clicking on a date takes you to sheet "Day"

Sheet Day

Here you can select a date in the top right calendar, you also select an hour and the corresponding event is shown in the window to the right.

Double click an event and you will be taken to sheet "Table" and the corresponding row. Likewise, if you double click an empty cell, a new row is created with date and time, see picture below.

Sheet Table

Download excel *.xlsm file

Yet another excel calendar.xlsm

This function must be entered as an array formula.

The following examples demonstrates how the function works.

Example 1

Array1 (B2:C3) has 2 rows and array2 (E2:F3) has 2 columns, the returning array has 2 rows and 2 columns.

Array formula in cell range L8:M9:

Cell L8: a₁₁*b₁₁+a₁₂*b₂₁ becomes 0*0+1*1 and returns 1

Cell L9: a₂₁*b₁₁+a₂₂*b₂₁ becomes 1*0+0*1 and returns 0

Cell M8: a₁₁*b₁₂+a₁₂*b₂₂ becomes 0*1+1*0 and returns 0

Cell M9: a₂₁*b₁₂+a₂₂*b₂₂ becomes 1*1+0*0 and returns 1

Example 2

Array1 (B2:C4) has 3 rows and array2 (E2:G3) has 3 columns, the returning array has 3 rows and 3 columns.

Array formula in cell range L8:M9:

Cell M8: a₁₁*b₁₁+a₁₂*b₂₁ becomes 0*1+1*4 and returns 4

Cell M9: a₂₁*b₁₁+a₂₂*b₂₁ becomes -4*1+0*4 and returns -4

Cell M10: a₃₁*b₁₁+a₃₂*b₂₁ becomes 3*1+1*4 and returns 7

Cell N8: a₁₁*b₁₂+a₁₂*b₂₂ becomes 0*2+1*-5 and returns -5

Cell N9: a₂₁*b₁₂+a₂₂*b₂₂ becomes -4*2+0*-5 and returns -8

Cell N10: a₃₁*b₁₂+a₃₂*b₂₂ becomes 3*2+1*-5 and returns 1

Cell O8: a₁₁*b₁₃+a₁₂*b₂₃ becomes 0*3+1*-6 and returns -6

Cell O9: a₂₁*b₁₃+a₂₂*b₂₃ becomes -4*3+0*-6 and returns -12

Cell O10: a₃₁*b₁₃+a₃₂*b₂₃ becomes 3*3+1*-6 and returns 3

Example 3

Array1 (B2:D3) has 2 rows and array2 (F2:G4) has 2 columns, the returning array has 2 rows and 2 columns.

Array formula in cell range L8:M9:

=MMULT(B8:D9,F8:G10)

Cell L8: a₁₁*b₁₁+a₁₂*b₂₁+a₁₃*b₃₁ becomes 1*0+2*-4+3*3 and returns 1

Cell L9: a₂₁*b₁₁+a₂₂*b₂₁+a₂₃*b₃₁ becomes 4*0+-5*-4+-6*3 and returns 2

Cell M8: a₁₁*b₁₂+a₁₂*b₂₂+a₁₃*b₃₂ becomes 1*1+2*0+3*1 and returns 4

Cell M9: a₂₁*b₁₂+a₂₂*b₂₂+a₂₃*b₃₂ becomes 4*1+-5*0+-6*1 and returns -2

Download excel *.xlsx file

MMULT.xlsx

SMALL(

Returns the k-th smallest value in a data set.

LARGE(array, k)

Returns the k-th largest value in a data set.

Arguments

array - Is a reference to a cell range or is an array of constants.

k - The position in the array of the value to return.

Example 1 - Cell range contains numbers, text and blanks

becomes

=SMALL({3; 6; "A"; 5; "X"; 0; 4; 4},3)

Text strings and blanks are overlooked. The array becomes

=SMALL({3; 6; ; 5; ; ; 4; 4},3)

and returns 4. 4 is the third smallest numerical value in the array.

Example 2 - Use a condition (Array formula)

The following array formula looks for the second smallest value in column C if corresponding value in adjacent column D is 2012-Dec:

becomes

=SMALL(IF({"2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Dec"; "2012-Nov"; "2013-Jan"; "2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Nov"; "2012-Dec"}="2012-Dec", C4:C14, ""), F4)

becomes

=SMALL(IF({FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}, C4:C14, ""), F4)

becomes

=SMALL({"";60.69;"";8.08;"";"";"";24.44;"";"";2.25}, 2)

and returns 8.08 in cell F7.

Download excel *.xlsx file

SMALL and LARGE function.xlsx

Copy a file

Sub CopyFile()
Dim src As String, dst As String, fl As String
'Source directory
src = Range("B3")
'Destination directory
dst = Range("D3")
'File name
fl = Range("B6")
On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & fl
    If Err.Number <> 0 Then
        MsgBox "Copy error: " & src & "\" & fl
    End If
On Error GoTo 0
End Sub

Copy and rename a file

Sub CopyRenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String
'Source directory
src = Range("B3")
'Destination directory
dst = Range("D3")
'File name
fl = Range("B6")
'Rename file
rfl = Range("D6")
On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & rfl
    If Err.Number <> 0 Then
        MsgBox "Copy error: " & src & "\" & rfl
    End If
On Error GoTo 0
End Sub

Rename a file

Sub RenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String
'Folder
src = Range("B3")
'File name
fl = Range("B6")
'Rename file
rfl = Range("D6")
On Error Resume Next
    Name src & "\" & fl As src & "\" & rfl
    If Err.Number <> 0 Then
        MsgBox "Error: " & src & "\" & rfl
    End If
On Error GoTo 0
End Sub

List files in a folder

Sub ListFilesinFolder()
Dim Value As String
Dim strt As Range
Set strt = Range("B6")
Value = Dir(Range("B3"), &H1F)
Do Until Value = ""
If Value <> "." And Value <> ".." Then
    strt = Value
    Set strt = strt.Offset(1, 0)
End If
Value = Dir
Loop
End Sub

List files in a folders and sub-folders

List files in a folder and subfolders

Download excel *.xlsm file

Copy rename files (excel vba).xlsm

I've 30 objects and 10 people, and I need to assign each person with randomly (unique) selected objects as a daily activity. Would you please suggest me on how I can do it using excel formulae?

Answer:

The objects are unique within that day. It means that a person can get the same object next day if unlucky. If this is not what you are looking for, let me know.

The array formula in cell C3:C32 returns a random unique value every time you press F9. The objects are in column F.  The table does not need to be sorted by date.

Array formula in cell C3:

How to enter an array formula

1. Select cell C3
2. Copy/Paste above array formula to formula bar
3. Press and hold CTRL + SHIFT
4. Press ENTER

Explaining array formula in cell C3

Step 1 - Calculate possible objects

COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0

becomes

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}=0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

All objects can be randomly selected. If there had been previous object that day they would have been ruled out.

Step 2 - Calculate row numbers

(COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32))

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30}

and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30}

Step 3 - Return a random number

RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3))

becomes

RANDBETWEEN(1, 31-1)

becomes

RANDBETWEEN(1, 30)

Step 4 - Return the k-th largest number

LARGE(array, k)

LARGE((COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32)), RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3)))

becomes

LARGE({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30}, RANDBETWEEN(1, 30))

and returns a random row number between 1 and 30.

Step 5 - Return a value

=INDEX($F$3:$F$32, LARGE((COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32)), RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3))))

becomes

=INDEX($F$3:$F$32, random_row_number)

becomes

=INDEX({"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"; "J"; "K"; "L"; "M"; "N"; "O"; "P"; "Q"; "R"; "S"; "T"; "U"; "V"; "X"; "Y"; "Z"; "AA"; "BB"; "CC"; "DD"; "EE"}, random_row_number)

and returns a random object.

Download excel *.xlsx file

Assign each person with randomly (unique) selected objects as a daily activity.xlsx

You can enter a reference to a single cell or a cell range. The ROW function returns an array of numbers if you enter a reference to a cell range. Remember to enter the formula as an array formula.

The reference argument is optional. Enter =ROW() in a cell and a reference to the cell itself is used. Example, =ROW() in cell F4 returns 4.

The first example demonstrates basic usage. Example 2 and 3 are more advanced and requires some knowledge about MATCH and INDEX function.

Example 1

A cell reference can be relative or absolute or a combination of relative and absolute.

Absolute and relative references in excel

In cell B2: =ROW(B2) returns 2.

In cell B6: =ROW() returns 6

In cell B11: =ROW($D$10) returns 10 (absolute cell reference).

Example 2 - Duplicate columns in an array

In cell range B3:B5:

returns {3; 4; 5}

Now you might wonder why the array formula doesn´t return {3, 3; 4, 4; 5, 5}. There are six cells in the cell range, how come only three values are returned?

The answer is that there is no need for multiple duplicate columns in the array. Excel simplifies the array down to a single column. But when used with multiple cell ranges in more complicated array formulas, make sure the number of rows match. See this example: Unique distinct values from a cell range

Example 3 - Row numbering a cell range

The INDEX function returns a value of the cell at the intersection of a particular row and column, in a given range. To be able to work with multiple values from an arbitrary cell range using the INDEX function we must number each row. That is exactly what we did in the previous example but the first row in a given range has to be 1 and the second 2 and so on. Why? The INDEX function needs properly numbered cell ranges.

This is where the ROW and MATCH function comes in.

The beauty with this formula is that it row numbers any cell range with any size.

In this example, the cell range is C3:D5:

becomes =MATCH({3; 4; 5}, {3; 4; 5}) and returns {1; 2; 3}.

Remember, enter the formula as an array formula.

So what can you do with row numbered cell ranges?

• How to return multiple values using vlookup
  
• Match two criteria and return multiple rows in excel

The MATCH function can also be used to find the relative position of a value in a cell range. See these posts:

• How to extract unique distinct values from a column
  
• Repeat values
• Fetch data from another table
• Shift Schedule

Download example file:

ROW function.xlsx