NEED Syntax HELP with Vlookup/Hlookup/Countif problem

[fergusor]

Hey!

I have been racking my brain and can't figure out the syntax needed for the following:

I need to count the number of registrants (time stamped) that signed-up for a given contest between a certain timeframe. I have attempted to visualize it below.

I have two tables, and need to do a lookup for the contest number across the row, then once matched, to then go down that column and count and sum all the time stamps that fit between my time restraints. I think I would need some sort of Sum, Countif, and Index syntax combination. I am trying to populate the Sum of Registrants column on the second table.

[TABLE="width: 500"]
<tbody>[TR]
[TD]User
[/TD]
[TD]Contest 1
[/TD]
[TD]Contest 2
[/TD]
[TD]Contest 2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Date / Time Registered
[/TD]
[TD]Date/Time Registered
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date / Time Registered
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date/Time Registered
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Contest
[/TD]
[TD]Date/Time
[/TD]
[TD]Date/Time + 2hrs
[/TD]
[TD]Sum of Registrants
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]

PLEASE HELP! THANK YOU

 

[fergusor]

Thank you Mark and Ben! It works well. Really appreciate the help.

Try this:

Layout

[TABLE="width: 664"]
<tbody>[TR]
[TD="width: 32, bgcolor: transparent"]User[/TD]
[TD="width: 98, bgcolor: transparent"]Contest #1[/TD]
[TD="width: 98, bgcolor: transparent"]Contest #2[/TD]
[TD="width: 98, bgcolor: transparent"]Contest #3[/TD]
[TD="width: 98, bgcolor: transparent"]Contest #4[/TD]
[TD="width: 98, bgcolor: transparent"]Contest #5[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 47, bgcolor: transparent"]Contest[/TD]
[TD="width: 98, bgcolor: transparent"]Date/Time[/TD]
[TD="width: 98, bgcolor: transparent"]Date/Time + 2hrs[/TD]
[TD="width: 98, bgcolor: transparent"]Sum of Registrants[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]10/02/2014 05:00[/TD]
[TD="bgcolor: transparent, align: right"]10/02/2014 07:00[/TD]
[TD="bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 10:00[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]04/02/2014 00:00[/TD]
[TD="bgcolor: transparent, align: right"]06/02/2014 10:30[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]12/02/2014 07:00[/TD]
[TD="bgcolor: transparent, align: right"]12/02/2014 05:00[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]10/02/2014 05:00[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]08/02/2014 11:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]14/02/2014 17:00[/TD]
[TD="bgcolor: transparent, align: right"]14/02/2014 19:00[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent, align: right"]03/02/2014 13:23[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]16/02/2014 17:00[/TD]
[TD="bgcolor: transparent, align: right"]16/02/2014 19:00[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[/TR]
</tbody>[/TABLE]

Formula

In K2

=COUNTIFS(INDEX($B$2:$F$5,,MATCH($H$1&" #"&$H2,$B$1:$F$1,0)),">="&$I2,INDEX($B$2:$F$5,,MATCH($H$1&" #"&$H2,$B$1:$F$1,0)),"<="&$J2)

Markmzz

 

[markmzz]

Thank you Mark and Ben! It works well. Really appreciate the help.

I'm glad to help and thank you for the feedback.

Markmzz