NEED Syntax HELP with Vlookup/Hlookup/Countif problem

F

fergusor

New Member
Joined
Apr 28, 2014
Messages
5
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Hi,

Welcome to the forums. If I understand you correctly you are looking to count the number of registrants between a given two hour period. Your sheet says Sum of Registrants, but I don't see any information to sum, so I'm assuming you mean to count. Please let me know if this is incorrect.

Here is a sample dataset I created:


Excel 2010
ABCD
1UserContest 1Contest 2Contest 3
213/15/14 1:12 PM1/27/14 11:45 AM2/7/14 2:24 PM
321/3/14 9:07 AM4/8/14 8:24 AM4/18/14 10:19 PM
434/24/14 5:45 PM3/29/14 10:48 AM1/2/14 12:00 PM
541/3/14 2:09 AM3/25/14 5:31 PM3/9/14 11:31 PM
652/22/14 2:24 PM2/13/14 6:43 PM2/12/14 11:16 PM
761/11/14 9:50 PM3/31/14 5:16 PM2/5/14 7:26 PM
872/20/14 6:00 PM4/25/14 5:31 PM1/25/14 5:02 AM
981/4/14 2:09 PM4/25/14 8:38 AM1/30/14 10:48 AM
1094/24/14 12:02 PM4/14/14 10:04 PM1/7/14 4:04 AM
11103/26/14 12:28 PM1/27/14 6:57 PM2/22/14 8:38 PM
12111/11/14 10:48 AM2/14/14 11:45 AM1/30/14 12:00 PM
13124/18/14 12:28 AM3/11/14 6:43 AM4/2/14 9:07 PM
14131/4/14 8:24 AM3/4/14 11:16 PM3/13/14 11:31 AM
15141/25/14 1:55 AM2/25/14 12:28 PM2/7/14 1:55 AM
16154/3/14 8:09 PM4/22/14 6:43 AM3/14/14 1:40 PM
17164/24/14 1:36 PM4/11/14 1:26 AM3/1/14 1:26 PM
18174/1/14 3:21 AM4/28/14 7:12 AM1/12/14 12:00 PM
19181/22/14 6:43 AM4/12/14 1:12 AM1/26/14 10:04 AM
20193/12/14 2:52 AM3/6/14 11:31 AM2/25/14 8:09 PM
21204/24/14 6:43 PM2/7/14 9:50 PM2/24/14 1:12 PM
22212/12/14 10:33 PM3/31/14 1:12 AM1/25/14 10:33 PM
23222/11/14 10:04 AM1/16/14 4:19 AM3/22/14 5:16 PM
Sheet1


This is what the calculation would like and the formulas for counting. Is this what you are after?


Excel 2010
FGHI
1ContestDate/TimeDate/Time + 2 hoursCount of Registrants
214/24/14 12:00 PM4/24/14 2:00 PM2
323/31/14 5:00 PM3/31/14 7:00 PM1
Sheet1
Cell Formulas
RangeFormula
G2=DATE(2014,4,24)+TIME(12,0,0)
G3=DATE(2014,3,31)+TIME(17,0,0)
H2=G2+2/24
H3=G3+2/24
I2=COUNTIFS(B2:B23,">"&G2,B2:B23,"<="&H2)
I3=COUNTIFS(C2:C23,">"&G3,C2:C23,"<="&H3)
 
Upvote 0
Hey!

Thank you so much for your help. You are correct, that I do just need a count or registrants. That works great!

Quick question. Is there a way to use the INDEX function or hlookup function so I can automate the process?

THANK YOU!





bschwartz said:
Hi,

Welcome to the forums. If I understand you correctly you are looking to count the number of registrants between a given two hour period. Your sheet says Sum of Registrants, but I don't see any information to sum, so I'm assuming you mean to count. Please let me know if this is incorrect.

Here is a sample dataset I created:

Excel 2010
ABCD
UserContest 1Contest 2Contest 3

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3/15/14 1:12 PM[/TD]
[TD="align: right"]1/27/14 11:45 AM[/TD]
[TD="align: right"]2/7/14 2:24 PM[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1/3/14 9:07 AM[/TD]
[TD="align: right"]4/8/14 8:24 AM[/TD]
[TD="align: right"]4/18/14 10:19 PM[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4/24/14 5:45 PM[/TD]
[TD="align: right"]3/29/14 10:48 AM[/TD]
[TD="align: right"]1/2/14 12:00 PM[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1/3/14 2:09 AM[/TD]
[TD="align: right"]3/25/14 5:31 PM[/TD]
[TD="align: right"]3/9/14 11:31 PM[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2/22/14 2:24 PM[/TD]
[TD="align: right"]2/13/14 6:43 PM[/TD]
[TD="align: right"]2/12/14 11:16 PM[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1/11/14 9:50 PM[/TD]
[TD="align: right"]3/31/14 5:16 PM[/TD]
[TD="align: right"]2/5/14 7:26 PM[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2/20/14 6:00 PM[/TD]
[TD="align: right"]4/25/14 5:31 PM[/TD]
[TD="align: right"]1/25/14 5:02 AM[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1/4/14 2:09 PM[/TD]
[TD="align: right"]4/25/14 8:38 AM[/TD]
[TD="align: right"]1/30/14 10:48 AM[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4/24/14 12:02 PM[/TD]
[TD="align: right"]4/14/14 10:04 PM[/TD]
[TD="align: right"]1/7/14 4:04 AM[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3/26/14 12:28 PM[/TD]
[TD="align: right"]1/27/14 6:57 PM[/TD]
[TD="align: right"]2/22/14 8:38 PM[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1/11/14 10:48 AM[/TD]
[TD="align: right"]2/14/14 11:45 AM[/TD]
[TD="align: right"]1/30/14 12:00 PM[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4/18/14 12:28 AM[/TD]
[TD="align: right"]3/11/14 6:43 AM[/TD]
[TD="align: right"]4/2/14 9:07 PM[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1/4/14 8:24 AM[/TD]
[TD="align: right"]3/4/14 11:16 PM[/TD]
[TD="align: right"]3/13/14 11:31 AM[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1/25/14 1:55 AM[/TD]
[TD="align: right"]2/25/14 12:28 PM[/TD]
[TD="align: right"]2/7/14 1:55 AM[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]4/3/14 8:09 PM[/TD]
[TD="align: right"]4/22/14 6:43 AM[/TD]
[TD="align: right"]3/14/14 1:40 PM[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4/24/14 1:36 PM[/TD]
[TD="align: right"]4/11/14 1:26 AM[/TD]
[TD="align: right"]3/1/14 1:26 PM[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]4/1/14 3:21 AM[/TD]
[TD="align: right"]4/28/14 7:12 AM[/TD]
[TD="align: right"]1/12/14 12:00 PM[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1/22/14 6:43 AM[/TD]
[TD="align: right"]4/12/14 1:12 AM[/TD]
[TD="align: right"]1/26/14 10:04 AM[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]3/12/14 2:52 AM[/TD]
[TD="align: right"]3/6/14 11:31 AM[/TD]
[TD="align: right"]2/25/14 8:09 PM[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4/24/14 6:43 PM[/TD]
[TD="align: right"]2/7/14 9:50 PM[/TD]
[TD="align: right"]2/24/14 1:12 PM[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]2/12/14 10:33 PM[/TD]
[TD="align: right"]3/31/14 1:12 AM[/TD]
[TD="align: right"]1/25/14 10:33 PM[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2/11/14 10:04 AM[/TD]
[TD="align: right"]1/16/14 4:19 AM[/TD]
[TD="align: right"]3/22/14 5:16 PM[/TD]

</tbody>
Sheet1​



This is what the calculation would like and the formulas for counting. Is this what you are after?

Excel 2010
FGHI
ContestDate/TimeDate/Time + 2 hoursCount of Registrants

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4/24/14 12:00 PM[/TD]
[TD="align: right"]4/24/14 2:00 PM[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3/31/14 5:00 PM[/TD]
[TD="align: right"]3/31/14 7:00 PM[/TD]
[TD="align: right"]1[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=DATE(2014,4,24)+TIME(12,0,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=G2+2/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=COUNTIFS(B2:B23,">"&G2,B2:B23,"<="&H2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]=DATE(2014,3,31)+TIME(17,0,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=G3+2/24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"]=COUNTIFS(C2:C23,">"&G3,C2:C23,"<="&H3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Maybe this:

Layout

[TABLE="width: 491"]
<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;" span="3"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;" span="2"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <tbody>[TR]
[TD="width: 32, bgcolor: transparent"]User[/TD]
[TD="width: 93, bgcolor: transparent"]Contest 1[/TD]
[TD="width: 93, bgcolor: transparent"]Contest 2[/TD]
[TD="width: 93, bgcolor: transparent"]Contest 3[/TD]
[TD="width: 17, bgcolor: transparent"] [/TD]
[TD="width: 44, bgcolor: transparent"]Contest[/TD]
[TD="width: 93, bgcolor: transparent"]Date/Time[/TD]
[TD="width: 93, bgcolor: transparent"]Date/Time + 2hrs[/TD]
[TD="width: 95, bgcolor: transparent"]Sum of Registrants[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2014 13:00[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 15:00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2014 13:00[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2014 15:00[/TD]
[TD="bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 13:00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 13:00[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 15:00[/TD]
[TD="bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2014 15:00[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 16:00[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 15:00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/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]
[/TR]
</tbody>[/TABLE]

Formula

Code: 
In I2

=COUNTIFS(INDEX($B$2:$D$4,,MATCH($F$1&" "&$F2,$B$1:$D$1,0)),">="&$G2,INDEX($B$2:$D$4,,MATCH($F$1&" "&$F2,$B$1:$D$1,0)),"<="&$H2)

Markmzz
 
Upvote 0
Hey Ben -

I have a large data set so I would like to use a INDEX or hlookup to take the contest number from table 2, match it with table 1, then to count the registrants (time stamp) that fall within the time range posted in table 2. Similar to what you have suggested, but rather than entering the column array manually, for the syntax to pull the column based on the mathcing of the contest number.

I hope that makes it more clear. THANK YOU !

Table 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]User
[/TD]
[TD="align: center"]Contest #1
[/TD]
[TD]Contest #2
[/TD]
[TD]Contest #3
[/TD]
[TD]Contest #4
[/TD]
[TD]Contest #5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][TABLE="width: 131"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2/3/14 1:23 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD][TABLE="width: 131"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2/3/14 10:00 AM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/4/14 12:00 AM
[/TD]
[TD]2/6/14 10:30 AM
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2/10/14 5:00 AM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/8/14 11:00 AM
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
​
[/TR]
</tbody>[/TABLE]


Table 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Contest #
[/TD]
[TD]Date/Time
[/TD]
[TD]Plus 2hrs
[/TD]
[TD]Count of Reg.
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2/10/14 5:00 AM
[/TD]
[TD]2/10/14 7:00 AM
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2/12/14 5:00 AM
[/TD]
[TD]2/12/14 7:00 AM
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2/14/14 5:00 AM
[/TD]
[TD]2/14/14 7:00 AM
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2/16/14 5:00 AM
[/TD]
[TD]2/16/14 7:00 AM
[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]




bschwartz said:
I'm sorry I don't understand your question. What process are you looking to automate?
Click to expand...
 
Upvote 0
Hey Ben -

I have a large data set so I would like to use a INDEX or hlookup to take the contest number from table 2, match it with table 1, then to count the registrants (time stamp) that fall within the time range posted in table 2. Similar to what you have suggested, but rather than entering the column array manually, for the syntax to pull the column based on the mathcing of the contest number.

I hope that makes it more clear. THANK YOU !

Table 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]User
[/TD]
[TD="align: center"]Contest #1
[/TD]
[TD]Contest #2
[/TD]
[TD]Contest #3
[/TD]
[TD]Contest #4
[/TD]
[TD]Contest #5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][TABLE="width: 131"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2/3/14 1:23 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD][TABLE="width: 131"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2/3/14 10:00 AM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/4/14 12:00 AM
[/TD]
[TD]2/6/14 10:30 AM
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2/10/14 5:00 AM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/8/14 11:00 AM
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
[TD]2/3/14 1:23 PM
[/TD]
​
[/TR]
</tbody>[/TABLE]


Table 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Contest #
[/TD]
[TD]Date/Time
[/TD]
[TD]Plus 2hrs
[/TD]
[TD]Count of Reg.
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2/10/14 5:00 AM
[/TD]
[TD]2/10/14 7:00 AM
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2/12/14 5:00 AM
[/TD]
[TD]2/12/14 7:00 AM
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2/14/14 5:00 AM
[/TD]
[TD]2/14/14 7:00 AM
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2/16/14 5:00 AM
[/TD]
[TD]2/16/14 7:00 AM
[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]




bschwartz said:
I'm sorry I don't understand your question. What process are you looking to automate?
Click to expand...
 
Upvote 0
Yes, I understand what you're after. See Markmzz post. I believe he has accomplished this:

markmzz said:
Maybe this:

Layout

[TABLE="width: 491"]
<tbody>[TR]
[TD]User[/TD]
[TD="width: 93, bgcolor: transparent"]Contest 1[/TD]
[TD="width: 93, bgcolor: transparent"]Contest 2[/TD]
[TD="width: 93, bgcolor: transparent"]Contest 3[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 44, bgcolor: transparent"]Contest[/TD]
[TD="width: 93, bgcolor: transparent"]Date/Time[/TD]
[TD="width: 93, bgcolor: transparent"]Date/Time + 2hrs[/TD]
[TD="width: 95, bgcolor: transparent"]Sum of Registrants[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2014 13:00[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 15:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2014 13:00[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2014 15:00[/TD]
[TD="bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 13:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 13:00[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 15:00[/TD]
[TD="bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2014 15:00[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 16:00[/TD]
[TD="bgcolor: transparent, align: right"]02/04/2014 15:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/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]
[/TR]
</tbody>[/TABLE]

Formula

Code: 
In I2

=COUNTIFS(INDEX($B$2:$D$4,,MATCH($F$1&" "&$F2,$B$1:$D$1,0)),">="&$G2,INDEX($B$2:$D$4,,MATCH($F$1&" "&$F2,$B$1:$D$1,0)),"<="&$H2)

Markmzz
Click to expand...
 
Upvote 0
Try this:

Layout

[TABLE="width: 664"]
<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" span="5"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" span="3"> <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

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Vlookup? Index Match?
Replies
8
Views
376
Peter_SSs
Peter_SSs
Tim_D
  • Solved
Is Vlookup and INDEX MATCH the best solution
Replies
3
Views
435
Fluff
Fluff
N
  • Question Question
Vlookup with max formula
Replies
1
Views
368
Fluff
Fluff
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
312
etaf
etaf
R
  • Solved
Excel/Google Sheets COUNTIFS formula to count rows, but with a substring in the Range.
2
Replies
11
Views
609
Fluff
Fluff

Forum statistics

Threads
1,224,589
Messages
6,179,744
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top