#VALUE!

S

Steved

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.
 
D

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!
 
S

Steved

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.
 
A

Aladin Akyurek

Why does this

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

lists the same item twice?
 
D

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!
 
S

Steved

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.
 
S

Steved

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.
 
A

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.
 
S

Steved

Thanks

Aladin Akyurek said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top