I'm not really clear on how you have this laid out, but you may want to try
this

=SUMPRODUCT(--(ISNUMBER(SEARCH("ABC",B6:B8,1))),(C6:C8))

I'm assuming that B6:B8 contain the values like "ABC 123", "ABC 111", etc.
C6:C8 contains values. You can change "ABC" to refence a cell as well.
Commit using CTRL SHIFT ENTER.

You can just put this inside an IF if you get what you want. Just don't
forget to commit with CTRL SHIFT ENTER when you change the formula at all.

--
HTH,
Barb Reinhardt




"franciz" wrote:

I need to lookup on 3 criteria and sum the corrsponding group then a "Y" or
"N' be input in column C

For example, Products in Column A such as XYZ, BBK....etc

Names in Column B such as ABC 01, ABCD 001, XY 0001 and so on....the names
can be varies in length, but we can identify by the group
name, like ABC xxx, XY xxx ...etc. there is always a space after the group
name


In Column D, I have Code like "US", "CA", "SG"...etc for the corresponding
rows and in column B, I have amount for related to each names.

Let say I need to first group "Product" in column A, then "Names" in column
B, and then "Code" in column D, then sum the "Amount" in column B for the
Group under the same Product. If the total sum of the amount is less than
100, put a "N" in Column C of the corresponding rows, otherwise put a "Y" if
the amount is greater than 100.