=SOMMEPROD((B12:B15>=B2)*(C12:C15<=C2)*(D12:D15))
[COLOR=DarkSlateGray][B]=SOMME(SI(SI(C12:C15<C$2;C12:C15;C$2)-SI(B12:B15>B$2;B12:B15;B$2)>0;SI(C12:C15<C$2;C12:C15;C$2)-SI(B12:B15>B$2;B12:B15;B$2);))[/B][/COLOR]
[COLOR=DarkSlateGray][B]Function toto(d1, d2, p1 As Range, p2 As Range)
Dim i&, x&, wf
toto = ""
If p1.Rows.Count <> p2.Rows.Count Then Exit Function
Set wf = Application.WorksheetFunction
For i = 1 To p1.Rows.Count
x = x + wf.Max(0, wf.Min(d2, p2.Cells(i, 1)) - wf.Max(d1, p1.Cells(i, 1)))
Next i
toto = x
End Function[/B][/COLOR]
[COLOR=DarkSlateGray][B]=toto(B2;C2;B12:B15;C12:C15)[/B][/COLOR]
Function toto(d1, d2, p1 As Range, p2 As Range)
Dim i&, x&, wf
toto = ""
If p1.Rows.Count <> p2.Rows.Count Then Exit Function
Set wf = Application.WorksheetFunction
For i = 1 To p1.Rows.Count
x = x + wf.Max(0, wf.Min(d2, p2.Cells(i, 1)) - wf.Max(d1, p1.Cells(i, 1))[COLOR="Red"] +1 )
' en cas d'absence de date pour éviter 366
If p1.Cells="" or p1.Cells="" then
toto = ""
Exit Function
End If[/COLOR]
Next i
toto = x
End Function
Function NbJourPlage(d1, d2, p1 As Range, p2 As Range)
Dim i&, x&, wf
NbJourPlage = ""
If p1.Rows.Count <> p2.Rows.Count Then Exit Function
Set wf = Application.WorksheetFunction
For i = 1 To p1.Rows.Count
x = x + wf.Max(0, wf.Min(d2, p2.Cells(i, 1)) - wf.Max(d1, p1.Cells(i, 1)) + 1)
If (p1.Cells(i, 1) = "") Or (p2.Cells(i, 1) = "") Then
NbJourPlage = ""
Exit Function
End If
Next i
NbJourPlage = x
End Function
=SOMME(SI(SI(C12:C15<C$2;C12:C15;C$2)-SI(B12:B15>B$2;B12:B15;B$2)+1>0;SI(C12:C15<C$2;C12:C15;C$2)-SI(B12:B15>B$2;B12:B15;B$2)+1;))
[COLOR=DarkSlateGray][B]Function toto(d1, d2, p1 As Range, p2 As Range)
Application.Volatile
Dim i&, x&, wf
toto = ""
If p1.Rows.Count <> p2.Rows.Count Then Exit Function
Set wf = Application.WorksheetFunction
For i = 1 To p1.Rows.Count
If IsDate(p1.Cells(i, 1)) And IsDate(p2.Cells(i, 1)) Then x = x + wf.Max(0, wf.Min(d2, p2.Cells(i, 1)) - wf.Max(d1, p1.Cells(i, 1)) + 1)
Next i
toto = x
End Function[/B][/COLOR]
Je ne sais pas le faire, mais ça ne prouve rien. On voit souvent des formules qu'on croyait impossibles, sur ce site…(…)
Pas beaucoup d'adepte du SOMMEPROD, peut-être pas possible?
(…)