5

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.

1

4 Answers 4

4

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

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

0

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

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.

You must log in to answer this question.

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