# Return Result based on Multiple Criteria

1. ## Return Result based on Multiple Criteria

Hi

I have been trying this for hours but to no avail.

I have a table with 4 columns headers
Name, Amount, Loc and Code

The name may look like ABC 1, ABC 2....
The Loc may be in US, GB...
and the Code may be AA, BB

I need to return a result "Y" if the sum of the amount is > 100
and "N" if the sum of the amount < 100 based on the conditions
of the following :if

1) Name is the same entity, such as ABC 1 and ABC 2 and
2) Loc is the same, US..and
3) Code is the same

I have attached a sample to illustrates the result

TIA  Register To Reply

2. ## Re: Return Result based on Multiple Criteria

=IF( SUMPRODUCT( (\$A\$2:\$A\$14=A2) * (\$C\$2:\$C\$14=C2) * (\$D\$2:\$D\$14=D2), \$B\$2:\$B\$14) >=100, "Y","N")

That assumes that the names are unique, rather than that ABC 1 is the same as ABC 2. If that's not the case, then you need to be specific about what makes a match.  Register To Reply

3. ## Re: Return Result based on Multiple Criteria

Hi shg

Thanks for this.
The names ABC 1 and ABC 2 although are unique but they fall under one entity, ie ABC
therefore the sum of ABC 1 and ABC 2 with the same Loc, eg US and the same Code eg
AA need to give the result of "Y"

The name can repeat in the table with the same Loc but under different Code.

The sample I have provided is a very small representation.

TIA  Register To Reply

4. ## Re: Return Result based on Multiple Criteria

My question was, what is the rule for determining that two entities are the same? The first three letters as in your example, or something more complex?  Register To Reply

5. ## Re: Return Result based on Multiple Criteria

It will be characters before a space or "-"

thanks  Register To Reply

6. ## Re: Return Result based on Multiple Criteria

Then I'd add a column that extracts the defining name,

=LEFT(A2, FIND(" ", SUBSTITUTE(A2 & " ","-", "") ) - 1)

... and apply the previous formula to that.  Register To Reply

7. ## Re: Return Result based on Multiple Criteria

Hi Shg

Thanks. This works fine!

Wondering if there any way to work in one formula instead of having a helper column?

TIA  Register To Reply

8. ## Re: Return Result based on Multiple Criteria

Yes, but it's needlessly redundant:

=IF( SUMPRODUCT( (LEFT(\$A\$2:\$A\$14, FIND(" ", SUBSTITUTE(\$A\$2:\$A\$14 & " ","-", "") ) - 1)=LEFT(A2, FIND(" ", SUBSTITUTE(A2 & " ","-", "") ) - 1)) * (\$C\$2:\$C\$14=C2) * (\$D\$2:\$D\$14=D2), \$B\$2:\$B\$14) >=100, "Y","N")  Register To Reply

9. ## Re: Return Result based on Multiple Criteria

Hi Shg

Thanks!

I wanna know of this is possible as I have been trying putting it in one formula.

TIA  Register To Reply

10. ## Re: Return Result based on Multiple Criteria

There is no helper column used in the last formula.  Register To Reply

11. ## Re: Return Result based on Multiple Criteria

Right...
I can't see my post and that why I repost again.
However, I have changed the contents of my last post.
Apology if that gave cause a confusion.  Register To Reply