Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Part of text as criteria
While Practising my skills I encountered following prob: Col 'B' = Region, C= Product and D is quantities Formula to sum quantities sold of product "Beta" to East region: =SUM(IF((B5:B10="EAST")*(C5:C10="beta"),D5:D10, 0)) (i hope the above formula is efficient enough) But when I put "Eastern" in any of the cell in Col 'B' it wont give me the desired result. So I want the 1st condition to look like B5:B10="East*" but even changing to this i could get the resuts. I have even tried B5:B10="East& "*"" but doesnt worked..... Please help! -- irresistible007 ------------------------------------------------------------------------ irresistible007's Profile: http://www.hightechtalks.com/m63 View this thread: http://www.hightechtalks.com/t2293650 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Part of text as criteria
Hi!
=SUM(IF((B5:B10="EAST")*(C5:C10="beta"),D5:D10, 0)) (i hope the above formula is efficient enough) More efficient (and, normally entered, not an array): A1 = East B1 = Beta =SUMPRODUCT(--(B5:B10=A1),--(C5:C10=B1),D5:D10) To do the same thing in which the cells in B5:B10 may contain the substring "East": =SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B5:B10))),--(C5:C10=B1),D5:D10) Biff "irresistible007" wrote in message ... While Practising my skills I encountered following prob: Col 'B' = Region, C= Product and D is quantities Formula to sum quantities sold of product "Beta" to East region: =SUM(IF((B5:B10="EAST")*(C5:C10="beta"),D5:D10, 0)) (i hope the above formula is efficient enough) But when I put "Eastern" in any of the cell in Col 'B' it wont give me the desired result. So I want the 1st condition to look like B5:B10="East*" but even changing to this i could get the resuts. I have even tried B5:B10="East& "*"" but doesnt worked..... Please help! -- irresistible007 ------------------------------------------------------------------------ irresistible007's Profile: http://www.hightechtalks.com/m63 View this thread: http://www.hightechtalks.com/t2293650 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Part of text as criteria
"irresistible007"
wrote in message ... While Practising my skills I encountered following prob: Col 'B' = Region, C= Product and D is quantities Formula to sum quantities sold of product "Beta" to East region: =SUM(IF((B5:B10="EAST")*(C5:C10="beta"),D5:D10, 0)) =SUMPRODUCT((LEFT(B5:B10,4)="EAST")* (C5:C10="beta")*D5:D10) Ciao Bruno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How change dimensions of data label text box in pie chart? | Charts and Charting in Excel | |||
Counting text criteria | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Count the text in a column - Part II | Excel Discussion (Misc queries) |