Matriz de contagens com número de dias

Question

Estou tentando ver quantos dias as pessoas estão por perto entre determinados blocos.

por exemplo. com as pessoas e os dias em que eles estão listados na primeira tabela (A1: C5) e os blocos na segunda tabela (A6: A10) e a resposta correta Estou tentando encontrar uma fórmula para próxima a ela (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    ║
╚═══╩══════════╩══════════╝

a resposta é como está acima porque Dave tem cerca de 45 dias no bloco 1 (15 de janeiro a 28 de fevereiro) e 55 dias no bloco 2 (de 1 de fevereiro a 24 de março). Resumindo que com os 12 dias de Jim no Bloco 1, os 35 dias de Gary no Bloco 1 e os 55 dias no Bloco 2, os 30 dias de Ted no Bloco 1 e os 23 dias de Sid no Bloco 1 dão os resultados mostrados em B6: B9.

Estou procurando uma fórmula que eu possa arrastar para baixo (suponho uma matriz) que me dará essas respostas.

Tem alguma ideia de alguém?

    

Answer 1

Parece que as datas de bloqueio se sobrepõem à sua descrição. Nesse caso, você precisará especificar as datas de início e término de cada bloco, portanto, com a data de início do bloco 1 em A7 e a data final em B7 use esta fórmula em C7 para o número de dias:

=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))

confirme com CTRL + SHIFT + ENTER e copie para baixo

Note que isso dá 137 para sua amostra - Gary tem apenas 27 dias, não 35

Atualização:

Encontrei uma maneira de reduzir o acima usando a função TEXT , ou seja,

=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;%code%")+0)