The WEEKDAY function returns a number, 1-7, that corresponds to particular days of the week. By default, WEEKDAY assumes a Sunday-based week, and assigns 1 to Sunday, 2 to Monday, and so on, with 7 assigned to Saturday.
In this case, we only want to take action if the date in question...Read more
This formula simply uses COUNTIF with a criteria of "x" (not quotation marks) to count x's in each row, where "x" represents "present" and an empty cell represents "absent":
=COUNTIF(C5:L5,"x") // count present
The count absent days, the...Read more
Data validation rules are triggered when a user adds or changes a cell value.
In this case, we need a formula that returns FALSE as long as entries in C6:C9 sum to a total equal to or below 1000. We use the SUM function to sum a fixed range and then simply compare the result to 1000...Read more
In this example, the goal is to calculate a sum for any given group ("A", "B", or "C") across all three months of data in the range C5:E16. In other words, we want to perform a "sum if" with a data range that contains three columns. For convenience only, data (C5:E16) and...Read more
In this example, the goal create a formula that will return TRUE when a volunteer has successfully logged the required number of hours in each of the three categories. Two requirements must be satisfied:
Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the LEFT function is used to extract the first 3 characters of the input in C5.
Next, the EXACT function is used to compare the extracted text to the text hard-coded into the formula...Read more
Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the FIND function is configured to search for the text "XST" in cell C5. If found, FIND will return a numeric position (i.e. 2, 4, 5, etc.) to represent the starting point of the text in the...Read more
This approach uses SUMPRODUCT to count the occurrence of every value in the table, one row at a time. Only when the same value appears in the same location in all three rows is a count generated. For cell B4, the formula is solved like this:
=SUMPRODUCT(($B...Read moreData validation rules are triggered when a user adds or changes a cell value.
The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison.
The AND function takes multiple arguments (logical conditions) and returns TRUE only...Read more
Data validation rules are triggered when a user adds or changes a cell value.
This custom validation formula uses the WEEKDAY function to get a numeric value, 1-7, corresponding to a week beginning Monday (1) and ending Sunday (7). To get a number for a Monday-based week, the ...Read more