Part of text as criteria

[irresistible007]

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"),D510, 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 m
the desired result. So I want the 1st condition to look lik
B5:B10="East*" but even changing to this i could get the resuts. I hav
even tried B5:B10="East& "*"" but doesnt worked.....

Please help

 

[Biff]

Hi!

=SUM(IF((B5:B10="EAST")*(C5:C10="beta"),D510, 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),D510)

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),D510)

Biff

"irresistible007" <[email protected]>
wrote in message

 

[Bruno Campanini]

"irresistible007" <[email protected]>
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"),D510, 0))

=SUMPRODUCT((LEFT(B5:B10,4)="EAST")*
(C5:C10="beta")*D510)

Ciao
Bruno