Sum If - (multiple criteria based on range)

[xtinct2]

hi,

I have a file and would like to use the sum if formula (i think) to look for a large amount of criteria, based on a range.

for example, if sheet2 has a (2 column) table that lists different foods (col. A) vs. quantities (col. B), such as:

Apple (2)
Meat (2)
Orange (4)
Crackers (5)
Grape (6)
Banana (10)
. . .

and a separate sheet in the file has a field for "Fruits", with a field next to it that would show the sum of all the fruit quantities in sheet1, how do I do it? I don't want to use the "AND" function, because what I I have 20+ fruits.

is there a way to use the sum if function to look up all criteria in a one-column range (listing all the fruits that show up in sheet2)?
thank you
(sorry if i was unclear)

[Ace_XL]

Something like this maybe..

=SUM(SUMIF(A1:A100,{"Apple","Banana","Grape",'Orange"},B1:B100))

[xtinct2]

Something like this maybe..

=SUM(SUMIF(A1:A100,{"Apple","Banana","Grape",'Orange"},B1:B100))

but what if there are 20 or even 50 fruit, is there a way to reference a range that lists them all?

[Muzza68]

just add a new column in sheet2 with a classification of "fruit" or otherwise, and use that as your condition.....

[Teethless mama]

but what if there are 20 or even 50 fruit, is there a way to reference a range that lists them all?

Try this:

Assuming your criteria in D1:D50

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A100,D1:D50,0))),B1:B100)

Adjust your range to suit

[xtinct2]

Try this:

Assuming your criteria in D1:D50

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A100,D1:D50,0))),B1:B100)

Adjust your range to suit

yes, thank you!!!

[xtinct2]

hi, me again,

lets say I have another column (3 columns in total) in sheet two that lists countries, and the field that returns the total fruit count has it per country for instance, Africa fruit quantity, Sweden fruit qty, etc.. Example:

Africa - Apple - 2
Sweden - Meat - 2
USA - Orange - 4
Africa - Crackers - 5
Africa - Grape - 6
USA - Banana - 10

the desired formula would return "8"

Thank you

[mozhils]

I recently had the same issue and thanks to help from this forum solved it with such approach
=SUMPRODUCT(SUMIFS(Data!C:C;Data!A:A;Result!C19;Data!B:B;Result!L1:L17))
where C19 has Your Africa and L1:L17 is the list of fruits.(assuming criteria is defined on the list with results.