# Thread: Show value based on the sum of multile conditions (Excel 2003)

1. ## Show value based on the sum of multile conditions (Excel 2003)

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.

TIA

regards, francis

2. ## Re: Show value based on the sum of multile conditions (Excel 2003)

Hi All

I have attached a sample to show the result.

Thanks
regards, francis

3. ## Re: Show value based on the sum of multile conditi

Cell E2, entered the formula and copied down

=IF(SUMIF(A:A,A2,C:C)>100,"Y","N")

Regards
Bosco

4. ## Re: Show value based on the sum of multile conditi

Hi Bosco

The formula is looking at column A and return the result in column E.
However, I need it to look at Col A, Col B and Col D with matching criteria, then return the result in Col E

I have derive a formula but it look at only Col B, sum the total in Col C and return the result in Col E
How do I include an additional condition for Col A and Col D

=IF(SUMIF(B:B,LEFT(B2,FIND(" ",C2&" ")-1) & "*",C:C)<100,"N","Y")

thanks

regards, francis

5. ## Re: Show value based on the sum of multile conditi

Something like this perhaps
=IF(SUMPRODUCT((\$A\$2:\$A\$100=A2)*(LEFT(\$B\$2:\$B\$100, FIND(" ",\$B\$2:\$B\$100&" "))=LEFT(B2,FIND(" ",B2&" ")))*(\$C\$2:\$C\$100)*(\$D\$2:\$D\$100=D2))>100,"Y"," N")

Note: Adjust the ranges as desired, you can not use the whole column (you will get a #NUM error). You should try to minimize the number of rows you do check as these types of arrays make the spreadsheet sluggish due to the vast number of calcuations each individual formula does.

Steve

6. ## Re: Show value based on the sum of multile conditi

Or……

=IF(SUMPRODUCT((\$A\$2:\$A\$100=A2)*ISNUMBER(FIND(LEFT (B2,FIND(" ",B2&" ")),B\$2:B\$100))*(\$C\$2:\$C\$100)*(\$D\$2:\$D\$100=D2))>10 0,"Y","N")

Regards
Bosco

7. ## Re: Show value based on the sum of multile conditi

This won't neccessarily yield the same results as what was asked for. for example, if B2 has "XL xxx" and there are items in B like "EXLAX xxxx" the "XL" will be found in the EXLAX but it will not match all of the beginning part.

Steve

8. ## Re: Show value based on the sum of multile conditi

Hi Steve

Great and thanks. I have approximately close to 1200 rows and I think it fine using this formula.
Appreciate if you can tell me how does this formula works?

TIA

cheers,

#### Posting Permissions

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