Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
    
Use an array formula –  Jon Fournier Feb 23 '09 at 14:42
add comment

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

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

4 Answers

up vote 2 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
add comment

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

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.