| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
#VALUE!
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. |
|
#2
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
Why does this
{"Contract Kilometres(Manuals)","Contract Kilometres(Manuals)"} lists the same item twice? 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. |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
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. "Domenic" wrote: 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. |
|
#7
|
|||
|
|||
|
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" wrote: Why does this {"Contract Kilometres(Manuals)","Contract Kilometres(Manuals)"} lists the same item twice? 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. |
|
#8
|
|||
|
|||
|
That's OK. When a single condition, the conditional becomes:
--(Range=Cond). When there are mo --ISNUMBER(MATCH(Range,CondList,0)), where CondList is an array like: {"X","Y","Z"} or a range that houses the conditions like: X2:Z2 Steved wrote: 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" wrote: Why does this {"Contract Kilometres(Manuals)","Contract Kilometres(Manuals)"} lists the same item twice? 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. -- [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. |
|
#9
|
|||
|
|||
|
Thanks
"Aladin Akyurek" wrote: That's OK. When a single condition, the conditional becomes: --(Range=Cond). When there are mo --ISNUMBER(MATCH(Range,CondList,0)), where CondList is an array like: {"X","Y","Z"} or a range that houses the conditions like: X2:Z2 Steved wrote: 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" wrote: Why does this {"Contract Kilometres(Manuals)","Contract Kilometres(Manuals)"} lists the same item twice? 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. -- [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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
