Hello All,

I have a sumproduct formula but I have one more criteria to add to it.
I have tried different things but it returns incorrect results.

The formula that I currently have is:

SUMPRODUCT(([March2005LockDate.xls]Create!$F$1:$F$50418="Retail
PA")*([March2005LockDate.xls]Create!$K$1:$K$50418="Conf15"))

What I need is for the formula to locate all cells with Retail PA and
Conf15 but also Conf10.

I don't know how to tell it to also look for Conf10.

Please help.

Thanks.


--
lacosta
------------------------------------------------------------------------
lacosta's Profile: http://www.excelforum.com/member.php...o&userid=15519
View this thread: http://www.excelforum.com/showthread...hreadid=472235

SUMPRODUCT(--([March2005LockDate.xls]Create!$F$1:$F$50418="Retail
PA"),--ISNUMBER(MATCH([March2005LockDate.xls]Create!$K$1:$K$50418,{"Conf10","Conf15"},0)))

lacosta wrote:
Hello All,

I have a sumproduct formula but I have one more criteria to add to it.
I have tried different things but it returns incorrect results.

The formula that I currently have is:

SUMPRODUCT(([March2005LockDate.xls]Create!$F$1:$F$50418="Retail
PA")*([March2005LockDate.xls]Create!$K$1:$K$50418="Conf15"))

What I need is for the formula to locate all cells with Retail PA and
Conf15 but also Conf10.

I don't know how to tell it to also look for Conf10.

Please help.

Thanks.



--

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

Hi,

Try this array formula (Ctrl+Shift+Enter)

=sum(if((range1="__")*(range2="__")*(range3="__"), 1,0))

Regards,

Ashish

"lacosta" wrote:


Hello All,

I have a sumproduct formula but I have one more criteria to add to it.
I have tried different things but it returns incorrect results.

The formula that I currently have is:

SUMPRODUCT(([March2005LockDate.xls]Create!$F$1:$F$50418="Retail
PA")*([March2005LockDate.xls]Create!$K$1:$K$50418="Conf15"))

What I need is for the formula to locate all cells with Retail PA and
Conf15 but also Conf10.

I don't know how to tell it to also look for Conf10.

Please help.

Thanks.


--
lacosta
------------------------------------------------------------------------
lacosta's Profile: http://www.excelforum.com/member.php...o&userid=15519
View this thread: http://www.excelforum.com/showthread...hreadid=472235

Thank you. It worked.


--
lacosta
------------------------------------------------------------------------
lacosta's Profile: http://www.excelforum.com/member.php...o&userid=15519
View this thread: http://www.excelforum.com/showthread...hreadid=472235