Using two conditions with a SUMIF function in Excel 2003

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.
Asked by: Guest \| Views: 343

Total answers/comments: 2

Comments display order:

Guest [Entry]

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

Guest [Entry]

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