Good Day,
I'm attempting to count the number of people who fall in a range of time... My current formula is not working - I've tried two.
On the Sheet 'march hc' row E contains start time 1:00:00 PM as an example while row F contains the stop time so 8:30:00 PM. Row G contains day of week so Monday, Tuesday, etc... I added in a row H for the sumif formula its just a basic counter holding the value of 1.
The row B on the main page where I'm doing the calculations contains time by hour so 1:00:00 PM, 2:00:00 PM, etc..
Formula 1 - not working at all.
=SUM(IF('march hc'!$E$2:$E$1438>=$B8,IF('march hc'!$F$2:$F$1438<$B9,IF('march hc'!$G$2:$G$1438=$C$2,'march hc'!$H$2:$H$1438,0),0)))
confirmed with ctrl+shift+enter
Formula 2 - working but providing results that do not match when verifying the data.
=SUMPRODUCT(--('march hc'!$E$2:$E$1438<=$B10),--('march hc'!$F$2:$F$1438>$B11),--('march hc'!$G$2:$G$1438="Wednesday"))
Any help on this would be great - Thank you so much for any time you spend on this! Have a great day!
I'm attempting to count the number of people who fall in a range of time... My current formula is not working - I've tried two.
On the Sheet 'march hc' row E contains start time 1:00:00 PM as an example while row F contains the stop time so 8:30:00 PM. Row G contains day of week so Monday, Tuesday, etc... I added in a row H for the sumif formula its just a basic counter holding the value of 1.
The row B on the main page where I'm doing the calculations contains time by hour so 1:00:00 PM, 2:00:00 PM, etc..
Formula 1 - not working at all.
=SUM(IF('march hc'!$E$2:$E$1438>=$B8,IF('march hc'!$F$2:$F$1438<$B9,IF('march hc'!$G$2:$G$1438=$C$2,'march hc'!$H$2:$H$1438,0),0)))
confirmed with ctrl+shift+enter
Formula 2 - working but providing results that do not match when verifying the data.
=SUMPRODUCT(--('march hc'!$E$2:$E$1438<=$B10),--('march hc'!$F$2:$F$1438>$B11),--('march hc'!$G$2:$G$1438="Wednesday"))
Any help on this would be great - Thank you so much for any time you spend on this! Have a great day!
