I have a list of employees (by ID number) and date (by yr & mon) of when they were assigned a certain duty (task). Problem description (simplified of course): Extract all rows from a range based on range criteria.Put the conditions on a row each in order to apply OR-logic instead of AND-logic between conditions, see image below. If both conditions match on the same row the record/row appears in the filtered list.

The image above shows records filtered on items based on condition in B3 and dates based on condition in C3.

Press with left mouse button on OK button.

Press with left mouse button on 'Criteria range:' field and select cell range B2:D3, see image above.

Press with left mouse button on radio button 'Filter the list, in place'.

Press with left mouse button on 'Advanced' button, a dialog box appears.

Type the conditions below each header accordingly.

Note, the filter values may become hidden if you place them next to the dataset. The position of a 0 (zero) or 1 in the array is important, the position matches the position in cell range B3:B19.ĬOUNTIF('SecurityB'. 1 indicates a match, we can use the array later on to match corresponding row numbers.

It returns an array containing as many values as there are cells in cell range B3:B19, the values can be 0 (zero) or 1. We can use the COUNTIf function to match the condition to values in cell range B3:B19. The COUNTIF function calculates the number of cells that is equal to a condition. Go to tab 'Formulas', press with the mouse on the 'Evaluate Formula' button.Ī dialog box opens, press the 'Evaluate' button to see formula calculations step by step. Use the 'Evaluate Formula' tool to examine a formula in great detail. Copy cell F9:H9 and paste down as far as needed.īack to top 1.4 Explaining excel array formula in cell range F9:H10 Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.Ĭopy cell F9 and paste it to the right.Back to top 1.3 How to create an array formula

How to add multiple rows in excel sheet