# Using two conditions with a SUMIF function in Excel 2003

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.

-

## migrated from stackoverflow.comNov 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

## 4 Answers

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))``
-

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

-

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)
``````
-

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.

-