Cell A1 has the text "apples" and cell A2 has "oranges". Cells B1:B20 have
the text names of many different fruits, including apples and oranges. Cells
C1:C20 have the prices I paid for each of the different fruits in B1:B20. I
want to add all of the amounts I paid for the apples AND oranges.
SUMIF(B1:B20, A1:A2, C1:C20) doesn't work because SUMIF won't accept a range
for the criteria - I can get a sum for one criteria (apples OR oranges), but
not both.

My real world application of this problem uses 11 different criteria instead
of just two. The criteria may change depending on what text is in the cells,
so I'd really like to just point to the cells these criteria are in and ask
Excel to see if my range matches any of those criteria, not just one of them.

Thanks!

Hi,

Can someone explain this one?

If the list range runs perpendicular to the data range this array formula
works fine.

{=SUM(IF(B4:B10=A2:C2,C4:C10))} ....works fine

but if the list range runs parrallel to the data range it doesn't work
unless the list range is of equal size as the data range.

{=SUM(IF(B4:B10=A2:A4,C4:C10))} ....doesn't work

Just curious.

Regards!
Jean-Guy

"Teethless mama" wrote:

Try this:

=SUMPRODUCT(ISNUMBER(MATCH(B1:B20,A1:A2,0))*(C1:C2 0))


"Kristopher" wrote:

Cell A1 has the text "apples" and cell A2 has "oranges". Cells B1:B20 have
the text names of many different fruits, including apples and oranges. Cells
C1:C20 have the prices I paid for each of the different fruits in B1:B20. I
want to add all of the amounts I paid for the apples AND oranges.
SUMIF(B1:B20, A1:A2, C1:C20) doesn't work because SUMIF won't accept a range
for the criteria - I can get a sum for one criteria (apples OR oranges), but
not both.

My real world application of this problem uses 11 different criteria instead
of just two. The criteria may change depending on what text is in the cells,
so I'd really like to just point to the cells these criteria are in and ask
Excel to see if my range matches any of those criteria, not just one of them.

Thanks!

I'd go with a series of sumifs, each one capturing the totals for a
particular fruit:
=sumif(b1:b20,a1,c1:c20) + sumif(b1:b20,a2,c1:c20), etc.
Alternately, with the list of 'acceptable' fruits in a1:a2, put a helper
column in D1:D20 indicating whether each row's entry is acceptable using a
match function: =not(isna(match(b1,$A$1:$A$2,0))). Then your total for the
selected fruits is just =sumif(d1:d20,true,c1:c20)

"Kristopher" wrote:

Cell A1 has the text "apples" and cell A2 has "oranges". Cells B1:B20 have
the text names of many different fruits, including apples and oranges. Cells
C1:C20 have the prices I paid for each of the different fruits in B1:B20. I
want to add all of the amounts I paid for the apples AND oranges.
SUMIF(B1:B20, A1:A2, C1:C20) doesn't work because SUMIF won't accept a range
for the criteria - I can get a sum for one criteria (apples OR oranges), but
not both.

My real world application of this problem uses 11 different criteria instead
of just two. The criteria may change depending on what text is in the cells,
so I'd really like to just point to the cells these criteria are in and ask
Excel to see if my range matches any of those criteria, not just one of them.

Thanks!