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

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

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