MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


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


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


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


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...


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!!!!