Как создать формулу, которая может считать значения по нескольким столбцам в указанных строках

Question

У меня есть лист Excel, где у меня есть несколько задач и несколько продуктов. Каждое задание для каждого продукта оценивается красным. янтарный или зеленый в зависимости от прогресса на данный момент. Каждое задание также имеет владельца.

Я хочу создать еще одну таблицу, которая показывает, сколько красных, янтарных и зеленых оценок назначено каждому владельцу. Я могу сделать это очень легко с помощью функции countif, но дополнительная проблема состоит в том, что новые задачи и владельцы добавляются в конец моего списка другими в моем отделе. Таким образом, единственный способ поддерживать свою таблицу в актуальном состоянии - постоянно изменять порядок таблиц по владельцам и корректировать диапазоны в счетчике.

Вот ссылка на пример, который я сделал:

Я нашел несколько функций Index, которые могут возвращать несколько значений и подсчитывать значения, но я недостаточно умен, чтобы заставить их работать в нескольких столбцах.

Спасибо,

Answer 1

Для вашего текущего макета вы можете использовать это в ячейке J4:

=SUMPRODUCT((OFFSET($D$3,0,0,COUNTA($C:$C)-1,COUNTA($2:$2)-2)=J$3)*(OFFSET($C$3,0,0,COUNTA($C:$C)-1)=$I4))

COUNTA($C:$C)-1 возвращает высоту таблицы данных (-1 означает игнорирование строки заголовка). Это основывается на столбце C, который содержит владельцев, и после последнего владельца не должно быть данных или пробелов между строками в самих данных.

COUNTA($2:$2)-2 аналогичным образом возвращает ширину таблицы данных (и на этот раз у меня есть -2 потому что есть 2 столбца до начала набора данных). Опять же, в таблице не должно быть пропущенных заголовков.

(OFFSET($D$3,0,0,COUNTA($C:$C)-1,COUNTA($2:$2)-2) возвращает диапазон, начинающийся с D3 и с высотой и шириной, вычисленными выше.

OFFSET($C$3,0,0,COUNTA($C:$C)-1) возвращает диапазон, содержащий владельцев.

(OFFSET($D$3,0,0,COUNTA($C:$C)-1,COUNTA($2:$2)-2)=J$3) сравнивает данные с ячейкой J3. В моей таблице я поместил r в J3, чтобы я мог перетаскивать формулу по таблице только два раза, не меняя ничего в формуле, но вы можете заменить J$3 на "r" , вам просто нужно отрегулировать его для каждого столбца один раз. Выражение вернет TRUE для совпадений и FALSE противном случае.

(OFFSET($C$3,0,0,COUNTA($C:$C)-1)=$I4) сравнивает владельцев с владельцем в ячейке I4. Возвращает TRUE для совпадений и FALSE противном случае.

Когда два вышеупомянутых результата умножаются вместе, вы получаете последовательность из 1 и 0 которую SUMPRODUCT складывает для подсчета.