That's probably because Column I and/or Column J contains formula
blanks. Try the following formula instead...
=SUM(IF(ISNUMBER('From Charters'!$I$1:$J$898)*ISNUMBER(MATCH('From
Charters'!$F$1:$F$898,{"Contract Kilometres(Manuals)","Contract
Kilometres(Manuals)"},0))*('From
Charters'!$B$1:$B$898="Period.4"),('From Charters'!$I$1:$J$898)))
....confirmed with CONTROL+SHIFT+ENTER. Also, as Aladin has pointed out,
both values for the array constant...
{"Contract Kilometres(Manuals)","Contract Kilometres(Manuals)"}
....are the same. I'm assuming that these two values should in fact be
different. Otherwise, you can replace...
ISNUMBER(MATCH('From Charters'!$F$1:$F$898,{"Contract
Kilometres(Manuals)","Contract Kilometres(Manuals)"},0))
with
('From Charters'!$F$1:$F$898="Contract Kilometres(Manuals)")
Hope this helps!
In article ,
Steved wrote:
Hello Domenic from Steved
I copied it and it is giving me #VALUE!
I tried it as an array and it still gave me #VALUE! (Ctrl Shift Enter)
What am I not doing.
"Domenic" wrote:
Try...
=SUMPRODUCT(ISNUMBER(MATCH('From Charters'!$F$1:$F$898,{"Contract
Kilometres(Manuals)","Contract Kilometres(Manuals)"},0))*('From
Charters'!$B$1:$B$898="Period.4")*('From Charters'!$I$1:$J$898))
Hope this helps!
In article ,
Steved wrote:
Hello from Steved
=SUMPRODUCT(--ISNUMBER(MATCH('From Charters'!$F$1:$F$898,{"Contract
Kilometres(Manuals)","Contract Kilometres(Manuals)"},0)),--('From
Charters'!$B$1:$B$898="Period.4"),'From Charters'!$I$1:$I$898)
from this 'From Charters'!$I$1:$I$898) to'From Charters'!$I$1:$J$898)
gives a #VALUE!.
Basically i'm trying to sum 2 cells Charters'!$I$1:$I$898 and
Charters'!$J$1:$J$898
Thankyou.
|