# 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

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.

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

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?

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

It will be characters before a space or "-"

thanks

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.

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

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")

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

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

There is no helper column used in the last formula.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1