Ваша формула возвращает ошибку, потому что диапазоны в Sumifs должны быть одинакового размера, но первый диапазон имеет переменную длину из-за конструкции с Indirect(), тогда как второй диапазон является фиксированным диапазоном.
Indirect() - это одна из возможностей построения диапазонов переменных, и вы можете применить ту же технику ко второму диапазону, например так:
=SUMIFS(INDIRECT("$B"&$B9):INDIRECT("$B"&$B10),INDIRECT("$A"&$B9):INDIRECT("$A"&$B10),D$11)
Обратите внимание, что я добавил знаки $ для ссылок B9 и B10, поэтому столбец не изменяется при копировании формулы.
Имейте в виду, что Indirect нестабилен, но чрезмерное использование его в больших книгах может привести к замедлению работы. Другой способ написать формулу - использовать энергонезависимый Index(), например:
=SUMIFS(INDEX($B:$B,$B9):INDEX($B:$B,$B10),INDEX($A:$A,$B9):INDEX($A:$A,$B10),D$11)