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.
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'
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:
Name this column (for example, "Criteria") then use that range name in the SUMIF. For example:
Use array formulas like this:
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.