SUMIF or IF or ???

[Vinay]

Prolly a simple question.

I need a way to sumif with multiple CHOICES, not criteria.

In other words something like this ..

=sumif(a1:a200,or("Banana","Lemon"),b1:b200)

Id rather not use the formula twice as I am referencing an external workbook
and have many "Fruits" to choose from. The formula essentially becomes too
long if I add up too many "Fruits"

[Bob Phillips]

=SUMPRODUCT((A1:A200={"Banana","Lemon"})*(B1:B200))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vinay" <[email protected]> wrote in message
news:[email protected]...
> Prolly a simple question.
>
> I need a way to sumif with multiple CHOICES, not criteria.
>
> In other words something like this ..
>
> =sumif(a1:a200,or("Banana","Lemon"),b1:b200)
>
> Id rather not use the formula twice as I am referencing an external
workbook
> and have many "Fruits" to choose from. The formula essentially becomes too
> long if I add up too many "Fruits"

[Bob Phillips]

Also, maybe put the fruits in a range and use

=SUMPRODUCT((ISNUMBER(MATCH(A1:A200,L1:L20,0)))*(B1:B200))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vinay" <[email protected]> wrote in message
news:[email protected]...
> Prolly a simple question.
>
> I need a way to sumif with multiple CHOICES, not criteria.
>
> In other words something like this ..
>
> =sumif(a1:a200,or("Banana","Lemon"),b1:b200)
>
> Id rather not use the formula twice as I am referencing an external
workbook
> and have many "Fruits" to choose from. The formula essentially becomes too
> long if I add up too many "Fruits"

[Vinay]

Awesome !!!! ... I tried this minus one set of brackets around the array
boolean formula. Without that it doesnt work ... Im guessing cause the
multiplication then occurs before the boolean check.

Thanks for your help. I appreciate it.

"Bob Phillips" wrote:

> =SUMPRODUCT((A1:A200={"Banana","Lemon"})*(B1:B200))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Vinay" <[email protected]> wrote in message
> news:[email protected]...
> > Prolly a simple question.
> >
> > I need a way to sumif with multiple CHOICES, not criteria.
> >
> > In other words something like this ..
> >
> > =sumif(a1:a200,or("Banana","Lemon"),b1:b200)
> >
> > Id rather not use the formula twice as I am referencing an external
> workbook
> > and have many "Fruits" to choose from. The formula essentially becomes too
> > long if I add up too many "Fruits"
>
>
>

[Loris]

if you only need to see the total forone tpe of fruit at a time, you might
try a database sum function like the following: Where Column A contains the
field name "Fruit", column B the field name "Amt." In the empty cells below
a cell that we will call Criteria, cell C1 in this example, type the field
name of the criteria you want to use, i.e., Fruit and belowthat, the name of
the fruit you want to sum. Let's call those two cells C2 and C3. In another
empty cell, (let's use D3) you put the formula: DSUM(A1:B29 [THE DATABASE
RANGE],2[THE COLUMN CONTANING THE VALUES YOU WANT TO SUM],C2:C3[THE RANGE
CONTAINING THE CRITERIA YOU WANT TO USE])
Fruit Amt Criteria
banana 2 Fruit
apple 3 peach 21
grape 4
peach 2
apple 5
banana 3
apple 4
banana 6
banana 7
apple 5
apple 6
banana 4
apple 5
grape 1
banana 2
grape 9
peach 6
apple 4
banana 3
apple 6
banana 7
banana 2
grape 1
peach 3
grape 2
grape 3
peach 4
peach 6


"Vinay" wrote:

> Prolly a simple question.
>
> I need a way to sumif with multiple CHOICES, not criteria.
>
> In other words something like this ..
>
> =sumif(a1:a200,or("Banana","Lemon"),b1:b200)
>
> Id rather not use the formula twice as I am referencing an external workbook
> and have many "Fruits" to choose from. The formula essentially becomes too
> long if I add up too many "Fruits"