You need to spell out your entire criteria, as explicitly as possible. Your information is very fractioned and difficult to follow. Let me try to understand this, because I do not fully.
1) You want to COUNT (not SUM), cells in column B of the MAIN worksheet, starting in row 8 and to the end of the column
2) You only want to count these if they are unique (same as your criteria
3) Your criteria is 'people count by hour'!$J$2
So, some obvious questions here. Regarding the numbered list above:
1) What's in column B? Do you have a header? Is your data contiguous? Does the last row change? Can we make it dynamic?
2) You posted a SUMIF function, but say you want to count by a specific criteria. Which is it? And whichever one it is, what is the criteria - exactly?
3) If J2 is your criteria, what is A4?
Please be very specific, otherwise you won't get a good answer.