Results 1 to 8 of 8

20080905, 06:53 #1
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20080905, 16:39 #2
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20080905, 19:34 #3
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
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

20080905, 20:43 #4
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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, francisHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20080906, 01:46 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20080906, 13:10 #6
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
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

20080907, 01:06 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20080913, 06:09 #8
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
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,Hope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array