Excluding Specific Date Range in SumIFs Formula

[Tres1981]

I have your standard sumifs formula:

Sumifs(Sales[Totals],Sales[Agent],"="&A1,Sales[Start Date],">="&E1,Sales[Start Date],"<"&F1....

I the dates in E1 and F1 are 6 months apart. I would like to exclude any dates/totals from this formula that are within a particular month in the middle of the period (Month Start/End dates are in cells G1/H1). Any ideas?

 

[ukmikeb]

Hi

Perhaps (not tested in SUMIFS) -

Sumifs(Sales[Totals],Sales[Agent],"="&A1,],OR(ISNUMBER(MATCH(Sales[Start Date],ROW(INDIRECT($E$1&":"&($G$1-1))),0)),ISNUMBER(MATCH(Sales[Start Date],ROW(INDIRECT($H$1+1&":"&$F$1)),0))),....

The ROW(INDIRECT... sets up an array of qualifying dates for the first and second periods respectively and the MATCH ensures that the Sales Date is in one of the periods.

The formula will have to be entered with Control - Shift - Enter as it is an Array Formula.

hth

 

[ukmikeb]

Hi

Or resort to SUMPRODUCT with -

=SUMPRODUCT(Sales[Totals],--(Sales[Agent]=$A$1),--(ISNUMBER(MATCH(Sales[Date],ROW(INDIRECT($E$1&":"&($G$1-1))),0)))+--(ISNUMBER(MATCH(Sales[Date],ROW(INDIRECT($H$1+1&":"&$F$1)),0))))

Btw there is a superfluous "]," in the formula in Post #2 before the OR.

hth