I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
=SUMPRODUCT(IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco Systems Capital Australia P/Lquot;)),quot;quot;))
PLEASE HELP.
THANKS.
DINESHquot;Dineshquot; wrote:
gt; I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
gt; =SUMPRODUCT(IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco Systems Capital Australia P/Lquot;)),quot;quot;))
One guess ..
D41055 is inconsistent with the other 2 ranges
Try changing it to D61055
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Tried..that was just a typo..didn't work.
Thanks.
Dinesh
quot;Maxquot; wrote:
gt; quot;Dineshquot; wrote:
gt; gt; I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
gt; gt; =SUMPRODUCT(IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco Systems Capital Australia P/Lquot;)),quot;quot;))
gt;
gt; One guess ..
gt; D41055 is inconsistent with the other 2 ranges
gt; Try changing it to D61055
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
You have a SUMPRODUCT function which contains a single argument which is an
IF function which in turn contains a SUMPRODUCT function. IMO the error comes
from the fact that the first SUMPRODUCT has only one argument which is also
not an array. SUMPRODUCT requires a minimum of 2 arguments which are arrays.
It seems that the first SUMPRODUCT isn't necessary. I don't know what your
requirement is. Perhaps this:
=IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
Systems Capital Australia P/Lquot;)),quot;quot;)
Regards,
Greg
quot;Dineshquot; wrote:
gt; I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
gt;
gt; =SUMPRODUCT(IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco Systems Capital Australia P/Lquot;)),quot;quot;))
gt;
gt; PLEASE HELP.
gt;
gt; THANKS.
gt;
gt; DINESH
gt;
quot;Dineshquot; wrote:
gt; Tried..that was just a typo..didn't work.
Perhaps you meant to do it as:
=IF(SUMPRODUCT(--($D$6:$D$1055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
Systems Capital Australia
P/Lquot;))=0,quot;quot;,SUMPRODUCT(--($D$6:$D$1055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco Systems Capital Australia P/Lquot;)))
If so, think it's easier/better to dispense with the error trap,
i.e. use just:
=SUMPRODUCT(--($D$6:$D$1055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco Systems Capital Australia P/Lquot;))
and just suppress extraneous zeros from display in the sheet via:
Tools gt; Options gt; View tab gt; Uncheck quot;Zero valuesquot; gt; OK
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Hi!
The reason you're getting #VALUE! is because you're using an IF array. The
formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER).
However, I'm guessing that you'll get incorrect results because the formula
isn't doing what you think it's doing. It'll iterate through the IF array
and for each element that is TRUE will execute the inner SUMPRODUCT.
Try this:
=SUMPRODUCT(--(D61055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
Systems Capital Australia P/Lquot;))
Biff
quot;Dineshquot; gt; wrote in message
...
gt;I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
gt;
gt; =SUMPRODUCT(IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
gt; Systems Capital Australia P/Lquot;)),quot;quot;))
gt;
gt; PLEASE HELP.
gt;
gt; THANKS.
gt;
gt; DINESH
gt;
Hi,
Below formula works. It doesn't give me a right answer if d6:d1055 isn't
equal quot;Mquot;. I am formulating with lt;gt;quot;Mquot;.
I also have to add one more element to it ...IF(c6:c1055=quot;Fquot; or quot;Oquot;...
which is more complicated for me.
=SUMPRODUCT(--(D61055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Australia P/Lquot;))
Any help is greatly appreciated.
Thanks.
Dineshquot;Biffquot; wrote:
gt; Hi!
gt;
gt; The reason you're getting #VALUE! is because you're using an IF array. The
gt; formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER).
gt;
gt; However, I'm guessing that you'll get incorrect results because the formula
gt; isn't doing what you think it's doing. It'll iterate through the IF array
gt; and for each element that is TRUE will execute the inner SUMPRODUCT.
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT(--(D61055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
gt; Systems Capital Australia P/Lquot;))
gt;
gt; Biff
gt;
gt; quot;Dineshquot; gt; wrote in message
gt; ...
gt; gt;I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
gt; gt;
gt; gt; =SUMPRODUCT(IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
gt; gt; Systems Capital Australia P/Lquot;)),quot;quot;))
gt; gt;
gt; gt; PLEASE HELP.
gt; gt;
gt; gt; THANKS.
gt; gt;
gt; gt; DINESH
gt; gt;
gt;
gt;
gt;
gt;It doesn't give me a right answer if d6:d1055 isn't
gt;equal quot;Mquot;. I am formulating with lt;gt;quot;Mquot;.
gt; =SUMPRODUCT(--(D61055=quot;Mquot;)
Doesn't look like you're formulating with lt;gt;quot;Mquot;.
So, you only want to count column D if it does not contain M?
Try this:
=SUMPRODUCT(--(ISNUMBER(MATCH(C6:C1055,{quot;Fquot;,quot;Oquot;},0))),--(D61055lt;gt;quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Australia
P/Lquot;))
Biff
quot;Dineshquot; gt; wrote in message
...
gt; Hi,
gt;
gt; Below formula works. It doesn't give me a right answer if d6:d1055 isn't
gt; equal quot;Mquot;. I am formulating with lt;gt;quot;Mquot;.
gt;
gt; I also have to add one more element to it ...IF(c6:c1055=quot;Fquot; or quot;Oquot;...
gt; which is more complicated for me.
gt;
gt; =SUMPRODUCT(--(D61055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Australia
gt; P/Lquot;))
gt;
gt; Any help is greatly appreciated.
gt;
gt; Thanks.
gt;
gt; Dinesh
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; The reason you're getting #VALUE! is because you're using an IF array.
gt;gt; The
gt;gt; formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER).
gt;gt;
gt;gt; However, I'm guessing that you'll get incorrect results because the
gt;gt; formula
gt;gt; isn't doing what you think it's doing. It'll iterate through the IF array
gt;gt; and for each element that is TRUE will execute the inner SUMPRODUCT.
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =SUMPRODUCT(--(D61055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
gt;gt; Systems Capital Australia P/Lquot;))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Dineshquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT(IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
gt;gt; gt; Systems Capital Australia P/Lquot;)),quot;quot;))
gt;gt; gt;
gt;gt; gt; PLEASE HELP.
gt;gt; gt;
gt;gt; gt; THANKS.
gt;gt; gt;
gt;gt; gt; DINESH
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
Hi Biff,
Here is a thing. Col C has only two values (F amp; O). Col D has 10 values(
EXAMPLE M, F, S ETC.). Col L has two values (New amp; Term), Col N has 10
values(Example AU, NZ etc).
I want to pick only one value from Col C (either F or O) - Col D, I want to
Pick either quot;Mquot; or the rest of remaining 9 values. I want pick one value from
each of the Col L and Col N.
Below formula gave me all under Col C.
Sorry for not being clear first time.
Thanks for your help.
Dinesh
quot;Biffquot; wrote:
gt; gt;It doesn't give me a right answer if d6:d1055 isn't
gt; gt;equal quot;Mquot;. I am formulating with lt;gt;quot;Mquot;.
gt; gt; =SUMPRODUCT(--(D61055=quot;Mquot;)
gt;
gt; Doesn't look like you're formulating with lt;gt;quot;Mquot;.
gt;
gt; So, you only want to count column D if it does not contain M?
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT(--(ISNUMBER(MATCH(C6:C1055,{quot;Fquot;,quot;Oquot;},0))),--(D61055lt;gt;quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Australia
gt; P/Lquot;))
gt;
gt; Biff
gt;
gt; quot;Dineshquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; Below formula works. It doesn't give me a right answer if d6:d1055 isn't
gt; gt; equal quot;Mquot;. I am formulating with lt;gt;quot;Mquot;.
gt; gt;
gt; gt; I also have to add one more element to it ...IF(c6:c1055=quot;Fquot; or quot;Oquot;...
gt; gt; which is more complicated for me.
gt; gt;
gt; gt; =SUMPRODUCT(--(D61055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Australia
gt; gt; P/Lquot;))
gt; gt;
gt; gt; Any help is greatly appreciated.
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt; Dinesh
gt; gt;
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; The reason you're getting #VALUE! is because you're using an IF array.
gt; gt;gt; The
gt; gt;gt; formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER).
gt; gt;gt;
gt; gt;gt; However, I'm guessing that you'll get incorrect results because the
gt; gt;gt; formula
gt; gt;gt; isn't doing what you think it's doing. It'll iterate through the IF array
gt; gt;gt; and for each element that is TRUE will execute the inner SUMPRODUCT.
gt; gt;gt;
gt; gt;gt; Try this:
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(D61055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
gt; gt;gt; Systems Capital Australia P/Lquot;))
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;Dineshquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
gt; gt;gt; gt;
gt; gt;gt; gt; =SUMPRODUCT(IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
gt; gt;gt; gt; Systems Capital Australia P/Lquot;)),quot;quot;))
gt; gt;gt; gt;
gt; gt;gt; gt; PLEASE HELP.
gt; gt;gt; gt;
gt; gt;gt; gt; THANKS.
gt; gt;gt; gt;
gt; gt;gt; gt; DINESH
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
Sounds like you need to use some drop down lists that list all the different
criteria then you can quot;mix-n-matchquot; all you want.
You'd create a drop down for each columns criteria:
A1 = Drop down for column C contains F;O
A2 = Drop down for column D contains M;F;S etc
A3 = Drop down for column L contains New;Term
A4 = Drop down for column N contains AU;NZ etc
Then, you'd simply refer to the cells that hold the drop down values:
=SUMPRODUCT(--(C6:C1055=A1),--(D61055=A2),--(L6:L1055=A3),--(N6:N1055=A4))
Biff
quot;Dineshquot; gt; wrote in message
...
gt; Hi Biff,
gt;
gt; Here is a thing. Col C has only two values (F amp; O). Col D has 10 values(
gt; EXAMPLE M, F, S ETC.). Col L has two values (New amp; Term), Col N has 10
gt; values(Example AU, NZ etc).
gt;
gt; I want to pick only one value from Col C (either F or O) - Col D, I want
gt; to
gt; Pick either quot;Mquot; or the rest of remaining 9 values. I want pick one value
gt; from
gt; each of the Col L and Col N.
gt;
gt; Below formula gave me all under Col C.
gt;
gt; Sorry for not being clear first time.
gt;
gt; Thanks for your help.
gt;
gt; Dinesh
gt;
gt;
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; gt;It doesn't give me a right answer if d6:d1055 isn't
gt;gt; gt;equal quot;Mquot;. I am formulating with lt;gt;quot;Mquot;.
gt;gt; gt; =SUMPRODUCT(--(D61055=quot;Mquot;)
gt;gt;
gt;gt; Doesn't look like you're formulating with lt;gt;quot;Mquot;.
gt;gt;
gt;gt; So, you only want to count column D if it does not contain M?
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =SUMPRODUCT(--(ISNUMBER(MATCH(C6:C1055,{quot;Fquot;,quot;Oquot;},0))),--(D61055lt;gt;quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Australia
gt;gt; P/Lquot;))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Dineshquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi,
gt;gt; gt;
gt;gt; gt; Below formula works. It doesn't give me a right answer if d6:d1055
gt;gt; gt; isn't
gt;gt; gt; equal quot;Mquot;. I am formulating with lt;gt;quot;Mquot;.
gt;gt; gt;
gt;gt; gt; I also have to add one more element to it ...IF(c6:c1055=quot;Fquot; or quot;Oquot;...
gt;gt; gt; which is more complicated for me.
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT(--(D61055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Australia
gt;gt; gt; P/Lquot;))
gt;gt; gt;
gt;gt; gt; Any help is greatly appreciated.
gt;gt; gt;
gt;gt; gt; Thanks.
gt;gt; gt;
gt;gt; gt; Dinesh
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi!
gt;gt; gt;gt;
gt;gt; gt;gt; The reason you're getting #VALUE! is because you're using an IF array.
gt;gt; gt;gt; The
gt;gt; gt;gt; formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER).
gt;gt; gt;gt;
gt;gt; gt;gt; However, I'm guessing that you'll get incorrect results because the
gt;gt; gt;gt; formula
gt;gt; gt;gt; isn't doing what you think it's doing. It'll iterate through the IF
gt;gt; gt;gt; array
gt;gt; gt;gt; and for each element that is TRUE will execute the inner SUMPRODUCT.
gt;gt; gt;gt;
gt;gt; gt;gt; Try this:
gt;gt; gt;gt;
gt;gt; gt;gt; =SUMPRODUCT(--(D61055=quot;Mquot;),--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
gt;gt; gt;gt; Systems Capital Australia P/Lquot;))
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Dineshquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; =SUMPRODUCT(IF(D41055=quot;Mquot;,SUMPRODUCT(--($L$6:$L$1055=quot;Newquot;),--($N$6:$N$1055=quot;Cisco
gt;gt; gt;gt; gt; Systems Capital Australia P/Lquot;)),quot;quot;))
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; PLEASE HELP.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; THANKS.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; DINESH
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
留言列表
