Explanation
The range B7:B9 contains the sheet names we want to include in the search. These are just text strings, and we need to do some work to get them to be recognized as valid sheet references.
Working from the inside out, this expression is used to build a full sheet reference:
"'"&B7&"'!"&"1:1048576"
The single quotes are added to allow sheet names with spaces, and the exclamation mark is a standard syntax for ranges that include a sheet name. The text “1:1048576” is a range that includes every row in the worksheet.
After B7 is evaluated, and values are concatenated, the expression above returns:
"'Sheet1'!1:1048576"
which goes into the INDIRECT function as the ‘ref_text’ argument. INDIRECT evaluates this text and returns a standard reference to every cell in Sheet1 . This goes into the COUNTIF function as the range. The criteria is provided as an absolute reference to C4 (locked so the formula can be copied down column C).
COUNTIF then returns a count of all cells with a value equal to “mary”, 25 in this case.
Note: COUNTIF is not case-sensitive.
Contains vs. Equals
If you want to count all cells that contain the value in C4, instead of all cells equal to C4, you can add wildcards to the criteria like this:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Now COUNTIF will count cells with the substring “John” anywhere in the cell.
Performance
In general, it’s not a good practice to specify a range that includes all worksheet cells. Doing so can cause performance problems, since the range includes millions and millions of cells. In this example, the problem is compounded, since the formula uses the INDIRECT function, which is a volatile function . Volatile functions recalculate on every worksheet change, so the impact on performance can be huge.
When possible, restrict ranges to a sensible size. For example, if you know data won’t appear past row 1000, you can search just the first 1000 rows like this:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
Explanation
In this example, the goal is to create a clickable link that will result in a ready-to-send email.
The mailto link protocol
The mailto link protocol allows five variables as shown in the table below:
| Variable | Purpose |
|---|---|
| mailto: | The primary recipient(s) |
| &cc= | The CC recipient(s) |
| &bcc= | The BCC recipient(s) |
| &subject= | The email subject text |
| &body= | The email body text |
Notes: (1) separate multiple email addresses with commas. (2) Not all variables are required.
The variables are presented as “query string parameters”, delimited with the ampersand (?) character. For example, a fully formed mailto: link in an HTML document might appear like this:
When a user clicks the link text, a new email will open in the default email application with the variables filled in.
Creating the link
In Excel, the HYPERLINK function can be used to create links. The basic syntax is:
=HYPERLINK("link","link text")
The link itself is a text string that represents a valid link. The link text (called “friendly name” in Excel) is the text displayed to a user.
Ultimately, the goal for the formula in G5 is to build a string like this:
mailto:aya@aa.com?cc=bb@bb.com&subject=subject&body=body
Because the mailto link uses several pieces of information, it must be assembled with concatenation .
The formula is a bit tricky. While the ampersand is the operator for concatenation in Excel, it is also used to delimit the mailto link parameters (cc, bcc, subject, etc.). This means that some ampersands (&) are used to join text in the formula, and some are embedded in the final result . In the code below, the ampersands in yellow are used for concatenation in Excel. The white ampersands are embedded in the final result:
=HYPERLINK("mailto:"&C5&"?"
&"cc="&D5
&"&subject="&E5
&"&body="&F5,
"link")
Note: the formula above is entered with line breaks for better readability.
Empty mailto parameters
For the sake of simplicity, the formula above does not try to exclude empty parameters from the final result. In quick testing with Gmail and Outlook, missing parameters seem to be ignored gracefully. The behavior in other email applications may vary.
