Explanation
Note: there are many ways to summarize data with COUNTIFS, SUMIFS, etc. This example shows one specific and arbitrary way. Before you go the formula route, consider a pivot table first , since pivot tables are far simpler to set up and do most of the hard work for you.
The table in B3:F11 is a log of course data where dates in columns D:F indicate course completion. The summary table in H3:K5 summarizes course completion by group instead of user. In this example, group represents the additional criteria.
For Course A, completion by group is calculated with COUNTIFS like this:
COUNTIFS($B$4:$B$11,$H4,D$4:D$11,"<>")
The first range/criteria pair counts only data from the red group by using the group value in H4. The second range/criteria pair counts non-blank entries in column D. The result is a count of all Course A completions for the Red group, 3 in this case.
To generate a total count of people in group Red, in order to calculate percent complete, another COUNTIFS is used:
COUNTIFS($B$4:$B$11,$H4)
The range/criteria pair counts total users in the red group by using the group value in H4, returning 3 in this case.
The result from the first COUNTIFS is divided by the result from the second COUNTIFS, and formatted with the Percentage number format to show percent complete.
Explanation
Note: In Excel 365 , the new SEQUENCE function is a better and easier way to create an array of numbers. The method explained below will work in previous versions.
The core of this formula is a string that represents rows. For example, to create an array with 10 numbers, you can hard-code a string into INDIRECT like this:
=ROW(INDIRECT("1:10"))
The INDIRECT function interprets this text to mean the range 1:10 (10 rows) and the ROW function returns the row number for each row in that range inside an array.
The example shown uses a more generic version of the formula that picks up the start and end numbers from B5 and C5 respectively, so the solution looks like this:
=ROW(INDIRECT(B5&":"&C5))
=ROW(INDIRECT(1&":"&5))
=ROW(INDIRECT("1:5"))
=ROW(1:5)
={1;2;3;4;5}
The reason INDIRECT is used in the formula is to guard against worksheet changes. Without INDIRECT, inserting or deleting rows can change the range reference, for example:
=ROW(1:5)
will change to:
=ROW(1:4)
If row 1 is deleted. Because INDIRECT works with a reference constructed with text, it isn’t affected by changes on the worksheet.
Relative row numbers in a range
If you need an array that consists of the relative row numbers of a range, you can use a formula like this:
=ROW(range)-ROW(range.firstcell)+1
See this page for a full explanation.
Negative values
The ROW function won’t handle negative numbers, so you can’t mix negative numbers in for start and end . However, you can apply math operations to the array created by ROW. For example, the following formula will create this array: {-5;-4;-3;-2;-1}
=ROW(INDIRECT(1&":"&5))-6
Numbers in reverse order, n to 1
To create an array of positive numbers in descending order, from n to 1, you can use a formula like this:
=ABS(ROW(INDIRECT("1:"&n))-(n+1))
