# sumif

#### simonf

##### Board Regular
I have colums: apple, banana, orange, pear, grape in row 1. The data are in row 2. I would like to sum only apple, orange, grape. Can it be done by SUMIF? Seems like it only allows on condition such as "apple". Thanks.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
On 2002-09-16 13:45, simonf wrote:
I have colums: apple, banana, orange, pear, grape in row 1. The data are in row 2. I would like to sum only apple, orange, grape. Can it be done by SUMIF? Seems like it only allows on condition such as "apple". Thanks.

You can do these by stringing sumif together to add them, or by excluding what you don't want to add. For example:
=SUM(A2:E2)-SUMIF(A1:E1,"banana",A2:E2)-SUMIF(A1:E1,"pear",A2:E2)

If this get to burdomsome, you may want to consider an array type formula such as:
=SUMPRODUCT(((A1:E1="apple")+(A1:E1="orange")+(A1:E1="grape")),A2:E2)

This could be at the expense of slower calculation times in a large sheet.

good luck

If your data where arranged in columns, you can use DSUM
Book1
ABCD
1FruitQuantityFruit
2Apple15Apple
3Banana20Orange
4Orange24Grape
5Pear34
6Grape60
799
...

Unfortunately, the data are in rows. I've actually considered both of your methods and was wondering if there's a shorter way of doing it. Thanks for all the helps anyway.

On 2002-09-16 14:03, simonf wrote:
Unfortunately, the data are in rows. I've actually considered both of your methods and was wondering if there's a shorter way of doing it. Thanks for all the helps anyway.

=SUMPRODUCT((ISNUMBER(MATCH(A1:D1,G1:I1,0))*A2:D2))

where A1:D1 houses the products, G1:I1 the target products (conditions), and A2:D2 the values to sum.

Sorry but I can't seem to get the formula to work. How would it apply if I want the Total After Markup per Quarter for Dept 10, 12, 20 and 21? Thanks.

Oooops, never mind, just got it to work. Thanks.

Actually, can you explain how this formula works? When I break it into individual parts, the MATCH part gave me #N/A and the ISNUMBER part gave me FALSE. When the whole thing combines together, it works! I would like to understand it so I can use the same technique to tackle future problems. Thanks.

Replies
6
Views
408
Replies
8
Views
336
Replies
3
Views
446
Replies
6
Views
262
Replies
3
Views
172

1,218,899
Messages
6,145,081
Members
450,590
Latest member
Naneng

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back