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
|