#VALUE!

[Guest]

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.

 

[Domenic]

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!

 

[Guest]

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.

 

[Aladin Akyurek]

Why does this

{"Contract Kilometres(Manuals)","Contract Kilometres(Manuals)"}

lists the same item twice?

 

[Domenic]

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!

 

[Guest]

Hello Domenic from Steved

Domenic when I went home last night I thought about your formula and said to
myself "yes it is not functioning because of the blank cell"

I once again thankyou.

 

[Guest]

Hello Aladin from Steved

{"Papakura","Wiri"},0))

If you look at the above you will see that it'll add column Papakura+Wiri
so I thought to myself I will put {"Contract Kilometres(Manuals)","Contract
Kilometres(Manuals)"} in to remind me how to add columns with Names. It might
look silly but it comes down to a Learning process for me.

Well that's my thinking on this issue.

Cheers.

 

[Aladin Akyurek]

That's OK. When a single condition, the conditional becomes:
--(Range=Cond). When there are more:
--ISNUMBER(MATCH(Range,CondList,0)), where CondList is an array like:

{"X","Y","Z"} or a range that houses the conditions like:

X2:Z2

Hello Aladin from Steved

{"Papakura","Wiri"},0))

If you look at the above you will see that it'll add column Papakura+Wiri
so I thought to myself I will put {"Contract Kilometres(Manuals)","Contract
Kilometres(Manuals)"} in to remind me how to add columns with Names. It might
look silly but it comes down to a Learning process for me.

Well that's my thinking on this issue.

Cheers.



:

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Guest]

Thanks

That's OK. When a single condition, the conditional becomes:
--(Range=Cond). When there are more:
--ISNUMBER(MATCH(Range,CondList,0)), where CondList is an array like:

{"X","Y","Z"} or a range that houses the conditions like:

X2:Z2

Hello Aladin from Steved

{"Papakura","Wiri"},0))

If you look at the above you will see that it'll add column Papakura+Wiri
so I thought to myself I will put {"Contract Kilometres(Manuals)","Contract
Kilometres(Manuals)"} in to remind me how to add columns with Names. It might
look silly but it comes down to a Learning process for me.

Well that's my thinking on this issue.

Cheers.



:

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.