Which Way Faster Sumproduct

[nanuutm]

I have data and need to check Date and Time. then check in list

if i get this Data "6/21/2016 11:50 5I" i check 1 in "6/22/2016" Below pic

i use sumproduct check time and date and before 11:00 or check time and date-1 and after 11:00

Sorry about my English

have other way to calculate Faster than SUMPRODUCT ?

 

[FormR]

Hi, something like this maybe? In this example the formula in D23 can be copied down and across.

Excel 2013
	B	C	D	E
22	Data		06/21/2016	06/22/2016
23	06/21/2016 11:50			1
24	06/21/2016 11:28			1
25	06/21/2016 11:24			1
26	06/21/2016 11:15			1
27	06/21/2016 10:14		1
28	06/21/2016 08:59		1
Sheet1

Cell Formulas
Range	Formula
D23	=IF(INT($B23)+(MOD($B23,1)>TIME(11,0,0))=D$22,1,"")

 

[nanuutm]

Hi, something like this maybe? In this example the formula in D23 can be copied down and across.

Excel 2013

	B	C	D	E
Data

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

[TD="align: right"][/TD]
[TD="align: right"]06/21/2016[/TD]
[TD="align: right"]06/22/2016[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]06/21/2016 11:50[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]06/21/2016 11:28[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]06/21/2016 11:24[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]06/21/2016 11:15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]06/21/2016 10:14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]06/21/2016 08:59[/TD]
[TD="align: right"][/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"]D23[/TH]
[TD="align: left"]=IF(INT($B23)+(MOD($B23,1)>TIME(11,0,0))=D$22,1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Like This

ex: 3H have 10.30 and 11.50 then 3H check 1 on 6/21/2016 and 6/22/2016

How to do table like this

 

[nanuutm]

now i found this

=COUNTIFS(Date,X$3,Time,"<11:00",Data,$A4)

if i want between Date and time in 1 Criteria what should i do

 

[FormR]

if i want between Date and time in 1 Criteria what should i do

Hi, don't you also need to take into account the "Data" column?

Anyway - here is one option you could try:

Excel 2013
	B	C	D	E	F	G
21					Answer
22	Data				06/21/2016	06/22/2016
23	06/21/2016 11:50	5I		5I		1
24	06/21/2016 11:28	5A		5A		1
25	06/21/2016 11:24	5B		4S
26	06/21/2016 11:15	4A		4A		1
27	06/21/2016 10:14	3U		3U	1
28	06/21/2016 08:59	6D		5L
29	06/21/2016 11:50	3H		6D	1
30	06/21/2016 10:30	3H		3H	1	1
31				5C
32				5B		1
Sheet1

Cell Formulas
Range	Formula
F23	=IF(COUNTIFS($C$23:$C$30,$E23,$B$23:$B$30,">="&F$22,$B$23:$B$30,"<="&F$22+TIME(11,0,0))+COUNTIFS($C$23:$C$30,$E23,$B$23:$B$30,">"&F$22-1+TIME(11,0,0),$B$23:$B$30,"<"&F$22),1,"")

For future reference, images are not a good way to describe your set-up - much better to use methods that 1) provide a permanent record and 2) allow any potential helpers to copy the set-up directly from the thread.

For ways to post example data, see post#2 here:

http://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[nanuutm]

Thank You FormR You help me so much