Sum of Certain Values

Posted by Michelle on May 03, 2001 6:43 AM

I am trying to sum values on a sheet, but only the values where a certain column of the sheet matches given criteria. In addition, there will be multiple values for that given criteria.

EXAMPLE:

Apples 3
Oranges 5
Grapes 9
Apples 1
Grapes 7
Banana 3
Tangerine 8
Oranges 6

I want to sum the values of apples...

Thanks in advance for the help.

Posted by Aladin Akyurek on May 03, 2001 7:08 AM

Michelle

Assuming that the data occupy the range A2:B9,
the following should be sufficient:

=SUMIF(A2:A9,D2,B2:B9)

where D2 contains a criterium value (say, Apples)

Posted by Mark W. on May 03, 2001 7:13 AM

Michelle, here's an array formula that will do the
trick...

{=SUM(ISNUMBER(MATCH(A1:A8,{"Apples"},0))*B1:B8)}

If you data included column headers you could also
use DSUM. If you not familiar with array formulas...
the must be entered using a Control+Shift+Enter key
combination. The braces, {}, are supplied by Excel --
not typed by you.

If you wanted to sum the values for both Apples and
Oranges, the use...

{=SUM(ISNUMBER(MATCH(A1:A8,{"Apples","Oranges"},0))*B1:B8)}

Posted by Aladin Akyurek on May 03, 2001 7:21 AM

Read the Q too fast...

Use array-formulas that Mark suggest in case of multiple criteria.

Posted by Michelle on May 03, 2001 12:52 PM

Thanks for the quick reply! I've got it working!!!!