Суммарная сумма в диапазоне, если соответствующая строка соответствует критериям

Question

Я пытаюсь создать электронную таблицу для расчета суммы часов в каждом месяце на основе соответствующих строк. Чтобы сделать пример немного более понятным, для Excel ниже я должен получить значение 28 для Клиента 1 и 16 для Клиента 3.

Я пытаюсь использовать функцию СУММЕСЛИ, но диапазон суммы не может охватывать несколько ячеек.

И вот как я хотел бы видеть информацию:

Answer 1

Вы можете создать простую формулу:

=SUM(IF(B5:B21="Client 1",G5:AK21))

Это выполнение вычисления массива в 2 шага:

1. Возвращает ВСЕ СТРОКИ чисел (даже пробелы), если столбец B = «Клиент 1».
2. Как только у меня будут все строки (большинство из которых будут пустыми), вы просто суммируете эти значения.

Вы можете заменить "Client 1"в формуле ссылку на внешнюю ячейку, если хотите. Также убедитесь, что весь диапазон месяца является точным во второй части формулы G5:AK21.

Answer 2

Опираясь на превосходный =SUM(IF())подход,@Дэйвможно использоватьOFFSETфункция выбора столбцов, соответствующих каждому из 12 месяцев года.

Функция OFFSETопределяет прямоугольный диапазон через его высоту и ширину, причем верхний левый угол диапазона определяется с точки зрения количества строк ниже и столбцов справа от указанной ячейки рабочего листа. В подходе ниже я использую ячейку, выделенную желтым цветом, как указанную ячейку (ячейка E17).

Диапазон A2:E14(см. скриншот ниже) предоставляет некоторую информацию, илиметаданные, о том, где расположены столбцы ежедневных значений для каждого из 12 месяцев года относительно выделенной ячейки. Полезные значения находятся в столбцах, озаглавленныхCol_OffsetиКолоннытак, например, для января ежедневные значения начинаются с 1-го столбца справа от желтой ячейки и занимают 31 столбец, для февраля ежедневные значения начинаются с 32-го столбца справа от желтой ячейки и занимают 29 столбцов (изменение года на невисокосный в ячейке изменит B1значение в ячейке E4на 28) и т. д. Столбцы, озаглавленныеМесяц#иНакопительныйпромежуточные значения, необходимые для расчетаCol_OffsetиКолонны.

Формулу для суммирования итоговых данных по месяцам и клиентам можно записать в разбивочном формате следующим образом:

=SUM(
IF(Sheet1!$B$18:$B$23=$A2,
OFFSET(Sheet1!$E$17,
1,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),
6,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4)
)
)
)

или, как полная формула

=SUM(IF(Sheet1!$B$18:$B$23=$A2,OFFSET(Sheet1!$E$17,1,VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),6,VLOOKUP(B$1,Sheet1!$A$3:$E$14,5))))

где предполагается, что метаданные и ежедневные данные содержатся на рабочем листе Sheet1.

Эта формула применяется к Январю/Клиенту 1 и может быть скопирована в оставшиеся ячейки для других месяцев и клиентов, как частично показано на снимке экрана ниже. Убедитесь, что названия месяцев, используемые в метаданных и таблице результатов, точно совпадают, чтобы избежать #N/Aошибок в результатах.

Второй и четвертый аргументы функции OFFSET— 1 и 6, поскольку данные начинаются в первой строке после выделенной желтым ячейки, и в показанном примере имеется 6 строк данных. Если имеется другое количество строк, просто подставьте правильное значение.

Третий и пятый аргументы просто используют функцию VLOOKUPдля поискаCol_OffsetиКолоннызначения в метаданных соответствующего месяца.