NOTIFYIFS
Description
Sends up to 1,000 notifications whenever one or more formulas with NOTIFYIFS are executed and all of their criteria are met. You can send a notification to a user whether or not they have access to the workbook. If the function exceeds the limit, notifications stop and the #ERROR indicator displays in the cell containing the function. When you hover the cursor over the cell, a message describes the error.
Syntax
NOTIFYIFS(
range1
, criteria1
, [ user_names
], [ subject
], [ message
], [ send_on_each
], [ range2
, criteria2
], ...) - range1: A range to evaluate the criteria for.
- criteria1: The string expression to evaluate against the range. The format is the same as functions such as SUMIF() and COUNTIF().
- user_names: The array or list of users to send the notification to. If you don't include this argument, the function uses the user_name of the current user (the user composing the function).
- subject: The text string to use as the subject of the Workday notification. If you include a subject, Workday adds a colon character at the end of the string.
- message: The text to use as the body of the notification. These HTML tags are supported: <br>, <b>, <i>, and <u>.
- send_on_each: If TRUE, a notification is sent on each value in the range that matches the criterion (TRUE). If FALSE, the notification is sent only if all the values in the range meet the criterion. The default is TRUE.
- range2: A range to evaluate the criteria for.
- criteria2: The string expression to evaluate against the range.
Example
A1:B3 contains:
1 2 1 2 1 2
Formula | Result |
|---|---|
=NOTIFYIFS(A1:A3,1,,,,false,B1:B3,2) | TRUE |
How NOTIFYIFS Works
The NOTIFYIFS function sends a notification whenever it executes and all conditions are true. It has no memory of whether a notification was previously sent for the same conditions. Use this function cautiously and test thoroughly before sharing a workbook to prevent unexpected notifications from being sent.
NOTIFYIFS will re-execute and send a notification if all conditions are true in any of the following situations:
- A cell that the NOTIFYIFS formula references, directly or indirectly, changes value.
- The formula contains a volatile function such as TODAY() or NOW(), and any cell in the workbook is changed.
- The NOTIFYIFS formula has a dependency on live data, and a live data refresh occurs.
- A manual recalculate all is triggered on the workbook.
Best practices for volatile functions
Volatile functions such as TODAY() and NOW() recalculate whenever any cell in the workbook changes. Using them in a NOTIFYIFS formula, either directly or through another formula it depends on, can cause notifications to fire every time a workbook edit occurs.
For example, suppose you want to notify a project owner when a high-priority project's deadline has passed. If column B contains project deadlines, column C contains priority levels, and your formula calls TODAY() directly, any edit anywhere in the workbook will cause your NOTIFYIFS to re-fire for every overdue high-priority project.
To prevent this, place =TODAY() in a dedicated cell such as H1 and reference H1 in your formulas instead of calling TODAY() directly. For example:
=NOTIFYIFS(B2,"<"&$H$1,"project.owner","Project Overdue","Your high-priority project deadline has passed.",TRUE,C2,"=High")
Since H1's value only changes once per day, your NOTIFYIFS will not re-execute on every workbook edit. It will still fire at least once per day if the workbook is changed in any way. Note that simply opening the workbook will not cause the formula to re-execute. A change to the workbook is required.
Only notify when conditions change from false to true
If your formula contains a volatile function such as TODAY() and you want to prevent duplicate notifications when the conditions have not changed, move the conditions into a separate helper column. For example, if you are monitoring whether a deadline in column B has passed and the priority in column C is "High", place =AND(B2<$H$1,C2="High") in a helper column and set that helper column as the range in your NOTIFYIFS with TRUE as the criteria. NOTIFYIFS will then only fire when the helper column's value changes from FALSE to TRUE rather than every time the volatile function causes a recalculation.
Live data refreshes
There is no configuration that prevents NOTIFYIFS from firing on a live data refresh if your formula has a dependency on live data. We recommend scheduling refreshes thoughtfully to manage notification volume.
Additional Notes
- This function evaluates a criterion expression against all the values in the range you specified. Ifsend_on_eachis true, then the function sends a notification for each criterion match in the range. Ifsend_on_eachis false, the function sends a notification only if all values in the match meet the criterion. The form of the criterion is the same as in SUMIFS() and COUNTIFS().
- The difference between NOTIFYIFS() and NOTIFYIF() is that in NOTIFYIFS(), the condition for sending a notification is met only if every criterion is met against its corresponding range. You can specify any number of range/criterion pairs; you must specify them as matching pairs. In addition, the shape of each range must match the shape of the first range (they must all have the same number of rows and columns). If we have three range/criterion pairs, then a condition is met if the criterion for each range is met for a specific cell in the range. Ifsend_on_eachis false, then the condition must be met for every cell in every range in order for the function to send a notification.
- The function generates a Workday notification. If emails are enabled in your environment, an email is also sent, but the email subject is auto-generated and doesn't match the subject argument of the notification.
- The function returns the value TRUE if it sent a notification or FALSE if it didn't.
- Workday identifies the sender of the notification as the user who placed the formula into the cell.
- Workday doesn't support using structured references with NOTIFYIFS.
Related Functions
NOTIFYIF