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.

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.

Hi Barb and all

The formula doesn't do what I want, its produce all "0" as a result.
I have apprend a sample table which show the layout and the result in
Col E


Col A Col B Col C Col D Col E
Product Name Amount Code Result
Xlcel KEN 2705 50 US Y
Excel KEN 7925 50 US N
xls TU 30 60 TH N
ex-cel FY 2309 70 SG Y
Xlcel KEN 5799 70 US Y
Excel 2003 TU 01 101 TH Y
Excel RON GB 80 GB N
ex-cel FY 45 70 SG Y


Thanks, xlsops


"Barb Reinhardt" wrote:

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.