SUM(INDEX(MATCH) for a range returns different result than SUM!
 

Hi.

Thanks to previous posters of this group I've learnt that in order to
have a sum in Sheet1 of the Sheet2 column that matches some expression
I must use this construct:

=SUM(INDEX(DATA,MATCH(D2,APPS,0)))

DATA:Sheet2!$B$1:$O$560, Data will not normally go beyond 200 rows, 560
is just for "security"
APPS:Sheet2!$B$1:$O$1 Headers are in B1 through O1


Now, the aforementioned formula evaluates to:
=SUM('Sheet2'!$F$1:$F$560)
which is quite good because F1 matches D2, the problem is that the
final result displayed is:
18:40:00 (data has h:mm:ss format and so has this cell)

while a normal
=SUM(('Sheet2'!F1:F560)
returns 1050:40:00 which is actually right

I copied the cell format over and over, defined DATA with or without
the headers but I cannot get the correct sum in place.

Any ideas?

Thanks for your time

SUM(INDEX(MATCH) for a range returns different result than SUM!
 

As Murphy would have said:

Just when you send your post you find the solution!

It seems that there more differences that one would have believed
between
h:mm:s format and [h]:mm:s format.

And that made the difference

ha escrito:

Hi.

Thanks to previous posters of this group I've learnt that in order to
have a sum in Sheet1 of the Sheet2 column that matches some expression
I must use this construct:

=SUM(INDEX(DATA,MATCH(D2,APPS,0)))

DATA:Sheet2!$B$1:$O$560, Data will not normally go beyond 200 rows, 560
is just for "security"
APPS:Sheet2!$B$1:$O$1 Headers are in B1 through O1


Now, the aforementioned formula evaluates to:
=SUM('Sheet2'!$F$1:$F$560)
which is quite good because F1 matches D2, the problem is that the
final result displayed is:
18:40:00 (data has h:mm:ss format and so has this cell)

while a normal
=SUM(('Sheet2'!F1:F560)
returns 1050:40:00 which is actually right

I copied the cell format over and over, defined DATA with or without
the headers but I cannot get the correct sum in place.

Any ideas?

Thanks for your time

SUM(INDEX(MATCH) for a range returns different result than SUM!
 

Perhaps it's just a formatting issue ..
Select the formula cell which returns: 18:40:00
Click Format Cells Number tab
Choose Custom, Type: [h]:mm:ss
(the square brackets around the "h" will prevent rollover)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Hi.

Thanks to previous posters of this group I've learnt that in order to
have a sum in Sheet1 of the Sheet2 column that matches some expression
I must use this construct:

=SUM(INDEX(DATA,MATCH(D2,APPS,0)))

DATA:Sheet2!$B$1:$O$560, Data will not normally go beyond 200 rows, 560
is just for "security"
APPS:Sheet2!$B$1:$O$1 Headers are in B1 through O1


Now, the aforementioned formula evaluates to:
=SUM('Sheet2'!$F$1:$F$560)
which is quite good because F1 matches D2, the problem is that the
final result displayed is:
18:40:00 (data has h:mm:ss format and so has this cell)

while a normal
=SUM(('Sheet2'!F1:F560)
returns 1050:40:00 which is actually right

I copied the cell format over and over, defined DATA with or without
the headers but I cannot get the correct sum in place.

Any ideas?

Thanks for your time