#Sum time in application.index#

I have data in C2:F10
Data in format time "hh:mm"
I want sum time using application.index
But result not correct

Please Login or Register  to view this content.

Is it vital that you use a macro for this? Won't a standard excel function do it sum the times - even if you need to add the function with a macro.
Why not upload the workbook and manually add the results you expect so that we can better understand.

Nevertheless please upload the workbook as requested.
You may be correct but I am by no means certain and will reserve judgment unti I can see the workbook.

@daboho- This procedure uses Excel's SUBTOTAL function - which is designed to work with filtering - to sum visible cells:

Please Login or Register  to view this content.

In the above code, the formula acts on the first 4 columns in CurrentRegion. Make sure they're the correct columns.

PS- In your original code, this line won't work:

Please Login or Register  to view this content.

Specialcells returns a multi-area range. If you try to assign its values to a variant, only the first area's values will be passed. However, you could store the multi-area range in an object variable:

Please Login or Register  to view this content.

this is file O1 = minDate,Q1 = maxdate and filter by nama in col N
leelnich is posibel to calculate time in value or in array vba ??
Richard Buttrey is posibel using formula ??

Here's TWO ways to do it in code. The simplest involves placing a straightforward SUMIFS formula on the sheet, then replacing it with VALUE. Of course, you could just enter the formula straight into the cells. I used full-column references here because the formula is temporary, but beware of using them in permanent formulas, as they can really slow down sheet calculation.

Please Login or Register  to view this content.

The second is more involved, using the Worksheet.Evaluate method to multiply Conditional Arrays, somewhat like a SUMPRODUCT formula. This is much more similar to your original approach with loops and such:

Please Login or Register  to view this content.

NOTE: Neither method addresses filtered rows, as both involve defining criteria by a different method. If this is an issue, the SUBTOTAL worksheet function mentioned earlier can be configured to ignore filtered rows, and might be worthy of investigation. Regards - Lee

Would you describe exactly what you are wanting to do and why for instance O3, O5 & O6 reference exactly the same cells.

It seems to me that you could probably use a Pivot Table and avoid formulae or VBA altogether. Although I admit at the moment I don't understand why you apparently want to exclude row 2 when it's within your criteria range.

Do you mean you don't know how to use a Pivot Table?
Tell us what you're trying to do and answer the implied question about why you are exclusing row 2.

Do that and mybe we can show you how to use a Pivot Table.

Originally Posted by daboho

second makro result is different with first coding...
matrik = hour(r.value)+minute(r.value)...

What is "r.value"?

My output from both (Number Format = "[h]:mm"):

Row\Col	N	O	P	Q	R	S	T
1	Tgl awal	2/3/2018	tgl akhir	2/13/2018
2	nama	scan masuk	scan pulang	terlambat	plg.cepat	Absen	Lembur
3	s	41:36	32:42	32:42	41:36	41:36	41:36
4	bg	20:48	20:48	9:54	20:48	20:48	20:48
5	s	41:36	32:42	32:42	41:36	41:36	41:36
6	s	41:36	32:42	32:42	41:36	41:36	41:36
7	b	46:42	46:42	46:42	46:42	46:42	46:42
8	k	14:54	14:54	14:54	14:54	14:54	14:54
9	c	16:54	16:54	16:54	16:54	16:54	16:54
10
11	s	41:36	32:42	32:42	41:36	41:36	41:36
12	bg	20:48	20:48	9:54	20:48	20:48	20:48
13	s	41:36	32:42	32:42	41:36	41:36	41:36
14	s	41:36	32:42	32:42	41:36	41:36	41:36
15	b	46:42	46:42	46:42	46:42	46:42	46:42
16	k	14:54	14:54	14:54	14:54	14:54	14:54
17	c	16:54	16:54	16:54	16:54	16:54	16:54

....why do you exclude row 2 in your example?

Ignore This... I thought Richard's post#14 addressed my output in post#13. Upon reflection, I realized his question was directed to OP.

Originally Posted by Richard Buttrey

....why do you exclude row 2 in your example?

Richard, the formula version uses full-column references:

Please Login or Register  to view this content.

...so I'm pretty sure row 2 is examined. I think a better question is:
"Why are there duplicate names in the list on the right?"

Originally Posted by leelnich

so I'm pretty sure row 2 is examined.

I don't believe so. The requirement seems to be to analyse data between 3/2/2018 & 13/2/2018 inclusive - else why specify those as criteria?
So why does the O3 forrmula for instance not mention E2?

Does the attached with a Pivot Table give you the sort of results you want.

I've only included an analysis of column E for simplicity.

Originally Posted by leelnich

Richard, the formula version uses full-column references:

Please Login or Register  to view this content.

...so I'm pretty sure row 2 is examined...

Originally Posted by Richard Buttrey

I don't believe so...why does the O3 formula for instance not mention E2?

Sorry, Richard, we were talking about 2 different formulas. I was referring to the one quoted. You were discussing the OP's ORIGINAL formula:

Please Login or Register  to view this content.

My mistake.