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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D23 =IF(INT($B23)+(MOD($B23,1)>TIME(11,0,0))=D$22,1,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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