Hi Biff,

Here is a thing. Col C has only two values (F & O). Col D has 10 values(
EXAMPLE M, F, S ETC.). Col L has two values (New & 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 "M" 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




"Biff" wrote:

It doesn't give me a right answer if d6:d1055 isn't
equal "M". I am formulating with <"M".
=SUMPRODUCT(--(D6:D1055="M")

Doesn't look like you're formulating with <"M".

So, you only want to count column D if it does not contain M?

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(C6:C1055,{"F","O"},0))),--(D6:D1055<"M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia
P/L"))

Biff

"Dinesh" wrote in message
...
Hi,

Below formula works. It doesn't give me a right answer if d6:d1055 isn't
equal "M". I am formulating with <"M".

I also have to add one more element to it ...IF(c6:c1055="F" or "O"...
which is more complicated for me.

=SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia
P/L"))

Any help is greatly appreciated.

Thanks.

Dinesh


"Biff" wrote:

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(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco
Systems Capital Australia P/L"))

Biff

"Dinesh" wrote in message
...
I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.

=SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco
Systems Capital Australia P/L")),""))

PLEASE HELP.

THANKS.

DINESH