Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

For a given row on Sheet1, I use the SUMIF function to see if the value from the first cell of that row is on a list of historical values in Sheet2.

That will basically summarize all the values on my historical sheet and group them by my A1 cell name on Sheet2.

I need a second condition so that I can group rows by month also, and the SUMIF does not seem to allow for a second condition or an AND to concatenate a second clause.

share|improve this question

migrated from stackoverflow.com Nov 2 '10 at 11:38

This question came from our site for professional and enthusiast programmers.

    
Use an array formula – Jon Fournier Feb 23 '09 at 14:42
up vote 4 down vote accepted

Assumptions:

Dates are in column B

Rows are 5:29

historical list in a range name 'Historical'

Month to test is in a range name 'rngMonth'

=SUMPRODUCT((NOT(ISNA(MATCH(A5:A29,Historical,FALSE))))*(MONTH(B5:B29)=rngMonth)*(C5:C29))
share|improve this answer

Try to add another SUMIF function which excludes those cells which you don't want to be SUMMed up & remove it from the main SUMIF.

e.g. SUMIF(all cells which fits large criteria) - SUMIF(all cells which are part of small criteria).

share|improve this answer

Alternate: on the sheet with historical data, add a column that concatenates the two columns you want to use as the combined conditions. For example, suppose one condition column is range-named "Month", the other condition column is range-named "PersonName":

The new column contains the following formula:

=Month&" "&PersonName

Name this column (for example, "Criteria") then use that range name in the SUMIF. For example:

=SUMIF(Criteria,"January Biff",ValsToBeSummed)
share|improve this answer

Use array formulas like this:

{=sum(If(sheet2!A1:A50=sheet1!A1,if(sheet2!B1:B50=Sheet1!B1,Sheet2!C1:C50)))}

Basically you nest your IF statements inside a SUM statement, then press Ctrl+Shift+Enter to make it an array formula.

Introduction to Array Forumulas (as posted by Jon Fournier) is also good resource on this.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .