Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF or IF or ???
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" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF or IF or ???
=SUMPRODUCT((A1:A200={"Banana","Lemon"})*(B1:B200) )
-- HTH RP (remove nothere from the email address if mailing direct) "Vinay" wrote in message ... 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" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF or IF or ???
Also, maybe put the fruits in a range and use
=SUMPRODUCT((ISNUMBER(MATCH(A1:A200,L1:L20,0)))*(B 1:B200)) -- HTH RP (remove nothere from the email address if mailing direct) "Vinay" wrote in message ... 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" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF or IF or ???
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" wrote in message ... 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" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF or IF or ???
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
Dynamic sumif function | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |