Hi,
I have been using a sumproduct formula for some years, and although it works fine I was wondering if there was an alternative way of getting the same result.
In C8 copied down =SUMPRODUCT(($A8>=$A$3:$A$4)*1,($B8<=$B$3:$B$4)*1,($C$3:$C$4)) to show how many are in each hour
[TABLE="width: 195"]
<tbody>[TR]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Staff[/TD]
[/TR]
[TR]
[TD]22:00[/TD]
[TD]04:00[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]06:00[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21:00[/TD]
[TD]22:00[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]22:00[/TD]
[TD]23:00[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]23:00[/TD]
[TD]00:00[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]01:00[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]01:00[/TD]
[TD]02:00[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]02:00[/TD]
[TD]03:00[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]03:00[/TD]
[TD]04:00[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]04:00[/TD]
[TD]05:00[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]05:00[/TD]
[TD]06:00[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]06:00[/TD]
[TD]07:00[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]07:00[/TD]
[TD]08:00[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
I have been using a sumproduct formula for some years, and although it works fine I was wondering if there was an alternative way of getting the same result.
In C8 copied down =SUMPRODUCT(($A8>=$A$3:$A$4)*1,($B8<=$B$3:$B$4)*1,($C$3:$C$4)) to show how many are in each hour
[TABLE="width: 195"]
<tbody>[TR]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Staff[/TD]
[/TR]
[TR]
[TD]22:00[/TD]
[TD]04:00[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]06:00[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21:00[/TD]
[TD]22:00[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]22:00[/TD]
[TD]23:00[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]23:00[/TD]
[TD]00:00[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]01:00[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]01:00[/TD]
[TD]02:00[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]02:00[/TD]
[TD]03:00[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]03:00[/TD]
[TD]04:00[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]04:00[/TD]
[TD]05:00[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]05:00[/TD]
[TD]06:00[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]06:00[/TD]
[TD]07:00[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]07:00[/TD]
[TD]08:00[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]