Использование Sumproduct на множестве различий в отдельных листах

Question

извините название, вероятно, не имеет смысла .. но вот что я пытаюсь сделать. У меня есть список проектов на листе с общим количеством текущих часов на проект на человека. У меня есть еще один лист со ставками (на человека, на разные годы). Затем у меня есть ведомость общих часов проекта на 31 декабря за несколько лет. Таким образом, лист 2017 года будет включать в себя все годы до 17, лист 2016 года, все годы до 2016 года и т.д. В настоящее время я пытаюсь сделать год 2017, и я могу основываться на этом, чтобы включить другие годы по мере необходимости. Вот что у меня есть, и я постараюсь объяснить:

  =SUMPRODUCT('rates'!B3:'rates'!J3 ,X8-'2017Projects'!B2:AF8-'2017Projects'!J2) + SUMPRODUCT('rates'!B2:'rates'!J2:,'2017Projects'!B2:'2017Projects'!J2)

Итак, что я пытаюсь сделать, это взять ставки на 2017 год (ставки B3-J3) и сделать суммарный результат часов текущего года минус часы предыдущего года (текущие часы в X8- '2017 Projects!B2 к ячейкам текущего AE8 - «2017Проекты»!L2 '), чтобы применить годовую ставку к этим годам часов. Затем, чтобы подвести итоги, я просто делаю краткое изложение часов на текущий день 2017 года, используя среднее значение, но я хотел бы также аналогично представить разницу между 17/16 часами, затем 16/15 часами и т.д. Похоже, что это не работает, как ожидалось, и вы не можете использовать сумму в sumproduct. Я видел SUMIF, но не могу заставить его работать так, как я хочу. Любое предложение для sumproduct с использованием разности 2 массивов? Спасибо!

РЕДАКТИРОВАТЬ: кстати, я хочу, чтобы это было в колонке общей стоимости после часов проекта, не включив это в образец ..

Расчеты, которые я пытаюсь сделать, следующие:

Sum of 
[
    rate b3 * (current hours X8 - 2017Projects B3) [171 * (3.5 - 0)] 
    rate c3 * (current hours Y8 - 2017Projects C3) [214 * (7.5 - 5.25)] 
    rate d3 * (current hours Z8 - 2017projects D3)
    ..etc
]

ссылка на образец файла: https://expirebox.com/download/81ee6c5f8d494097025dd06bf9b404d5.html

Answer 1

Вместо SUMPRODUCT я думаю, что вам нужна формула массива.

Если я правильно понимаю, у вас есть эти массивы, с которыми вы хотите выполнять математические операции:

'rates'!B3:J3 2017 Цены

X8:AF8 часов текущего года (кумулятивно на сегодняшний день с часами предыдущих лет)

'2017Projects'!B3:J3 2017 часов

И что вам нужно, так это 2017 Rates * (Current Year hours - часы 2017 hours) для каждого элемента, а затем сумма всех этих элементов.

=SUM('rates'!B3:J3*(X8:AF8-'2017Projects'!B3:J3))

Это формула массива. Введите его, а затем нажмите Ctrl-Shft-Enter. Ваша формула должна быть заключена в {} скобки.

Это вычитает массив '2017Projects'!B3:J3 из массива X8:AF8 , по одному элементу за раз, в результате чего создается новый массив с результатами этой операции, что означает, что первый элемент этого нового массива будет равен X8 - '2017Projects'!B3 , вторым элементом будет Y8 - '2017Projects'!C3 и т.д. Этот массив затем умножается, по одному элементу за раз, на массив 'rates'!B3:J3 . Полученный в результате массив затем суммируется.

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