Массив Countifs с количеством дней

Question

Я смотрю, сколько дней люди находятся между определенными кварталами.

например, с людьми и днями их присутствия, перечисленными в первой таблице (A1:C5) и блоками во второй таблице (A6:A10) и правильным ответом. Я пытаюсь найти формулу для следующего (B6) :B10)

╔═══╦══════════╦══════════╦══════╗
║   ║    A     ║    B     ║   C  ║
╠═══╬══════════╬══════════╬══════╣
║ 1 ║ 15/01/14 ║ 24/04/14 ║ dave ║
║ 2 ║ 24/01/14 ║ 04/02/14 ║  jim ║
║ 3 ║ 25/01/14 ║ 20/02/14 ║ gary ║
║ 4 ║ 10/01/14 ║ 08/02/14 ║  ted ║
║ 5 ║ 19/01/14 ║ 10/02/14 ║  sid ║
╚═══╩══════════╩══════════╩══════╝
╔═══╦══════════╦══════════╗
║   ║    A     ║    B     ║
╠═══╬══════════╬══════════╣
║ 6 ║ 01/01/14 ║   145    ║
║ 7 ║ 01/02/14 ║   106    ║
║ 8 ║ 01/04/14 ║     0    ║
║ 9 ║ 01/06/14 ║     0    ║
╚═══╩══════════╩══════════╝

ответ такой же, как и выше, потому что у Дэйва около 45 дней в блоке 1 (15 января - 28 февраля) и 55 дней в блоке 2 (1 февраля - 24 марта). Подводя итог, что с 12 днями Джима в блоке 1, 35 днями Гэри в блоке 1 и 55 днями в блоке 2, 30 днями Теда в блоке 1 и 23 днями Сида в блоке 1 дает результаты, показанные в B6:B9.

Я ищу формулу, которую я могу перетащить вниз (я предполагаю массив), которая получит мне эти ответы.

Есть какие-нибудь мысли?

Answer 1

Кажется, что даты блоков перекрываются с вашим описанием, и в этом случае вам нужно будет указать даты начала и окончания для каждого блока, поэтому для даты начала блока 1 в A7 и даты окончания в B7 используйте эту формулу в C7 для числа дней:

=SUM(IF((A$1:A$5>B7)+(B$1:B$5<A7),0,IF(B$1:B$5>B7,B7,B$1:B$5)-IF(A$1:A$5<A7,A7,A$1:A$5)+1))

подтвердите нажатием CTRL+SHIFT+ENTER и скопируйте вниз

Обратите внимание, что это дает 137 для вашего образца - у Гэри есть только 27 дней, а не 35

Обновить:

Я нашел способ сократить вышеупомянутое с помощью функции TEXT , т.е.

=SUM(TEXT(IF(B$1:B$5>B7,B7,B$1:B$5)-IF(A$1:A$5<A7,A7,A$1:A$5)+1,"0;\0")+0)