MINUS
Description
Returns all rows from a first range that do not appear in any of the other supplied ranges.
Syntax
MINUS(
range1
, [ range2
], ...) - range1: The range to subtract matching rows from.
- range2: The range to subtract from the base range. You can subtract any number of ranges from the start range.
Example
This is the original spreadsheet:
A | B | C | |
|---|---|---|---|
1
| Cost Center | Q1 | Q2 |
2
| 6010:Benefits Expenses | 6200:Marketing | 6010:Benefits Expenses |
3
| 6300:Office & Administrative | 4000:Revenue | 6100:Facilities Taxes |
4
| 6100:Facilities Taxes | 5000:Cost of Sales | 6300:Office & Administrative |
5
| 6400:Legal & Service Fees | 6870:Talent Acquisition | 6500:Information Technology |
6
| 6800:Travel & Entertainment | 6000:Salaries and Wages | 6700:Depreciation |
7
| 6870:Talent Acquisition | 6400:Legal & Service Fees | 6300:Office & Administrative |
The formula =MINUS(A2:A7,B2:B7,C2:C7), placed in cell A8, returns any values in the first range that are not present in subsequent ranges (cost centers that were not budgeted for in Q1 or Q2).
A | |
|---|---|
8
| 6800:Travel & Entertainment |
Notes
- The result of this function is the subset of rows fromrange1that do not appear in any of the other supplied range arguments. A row appears inrange1if all values in the row from a subsequent range are identical (and in the same order) to values inrange1.
- This function is intended for use in array formulas.
Related Functions
COMPARE