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

Topic

New Reply

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

Reply | Quote

Replies

Hi All

I have attached a sample to show the result.

Thanks
regards, francis

Reply | Quote

Cell E2, entered the formula and copied down

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

Regards
Bosco

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

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))>100,”Y”,”N”)

Regards
Bosco

Reply | Quote

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

Reply | Quote