Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an Excel sheet with three columns: date, person and percentage. I would like to put in a data validation that flags cells if the total for a given data/person combination do not equal 100%. Is that possible?

In other words, in the custom formula of a data validation, I would like to make the following type of formula.

 =if(sum( cells with a (date = the date on this row, person = person on this row))=1)

Is there a function which will return the cells in a range conditioned on certain values, or will sum the cells.

Note that if it is not possible to do two cells, I have no issue adding a cell which combines both values for the purpose of affecting the lookup.

share|improve this question
up vote 1 down vote accepted

You want SUMIF (for a single criteria), the Microsoft SUMIF page on it, is pretty good. as is the OfficeArticle's SUMIF.

Or just SUM with an array function (see above article from officearticles) for multiple criteria. For instance in the sheet below I entered the formula without curly braces into G2, and helt Ctrl+Shift+Enter, then I drug the formula down.

an excel array formula

If you have many many rows, then you'll need to move on to using DSUM for speed reasons. As you noted in your comment, they have to have their own little faux-table somewhere.

Really it sounds like you're quickly moving past what Excel can do without busting into VBA. A database, or targeted application may be in your near future.

edit: added information on array formula, pretty picture

share|improve this answer
Thanks for the info, but I'm not seeing how to do it. Even with DSUM, the criteria seem geared to a hard coded value. I want the criteria to be the column name on top and the value in my current row. How can I do that? – Yishai Dec 29 '09 at 0:35
Very cool, thanks! – Yishai Dec 29 '09 at 23:21

I know that your question has already been answered, but I thought I might bring your attention to the SUMPRODUCT() function. It's like SUMIF, but I personally find it easier to work with and easier to use AND or OR nested within

=Sumproduct((Range=Cell)*((Randge=ThisCell)+(Range=ThatCell))*(The figures to add up))

Hope this helps.


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.