CODEWITHSUNDEEP
excelexcel-formula
AlmostThere
AlmostThere

Reputation: 657

Sumproduct between dates with additional criteria across columns

Im trying to put together a sumproduct formula that looks between two dates and has a criteria across columns.

I have the following:

Start Date: Sheet1!A1

End Date: Sheet1!A2

Criteria Field: Sheet1!A3

I want to sum columns Sheet2!B2:F200 where Sheet2!A2:A200 is between the Start and End dates on Sheet1 and where Sheet2!B1:F1 equal the criteria field from Sheet1

I made it to here but I'm not able to sum across columns or add in the criteria field.

SUMPRODUCT(--(Sheet2!A2:A200>=Sheet1!A1),--(Sheet2!A2:A200<=Sheet1!A2),Sheet2!C:C)

Upvotes: 1

Views: 2355

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use SUMIFS with INDEX to return the correct column:

=SUMIFS(INDEX(Sheet2!$B:$F,0,MATCH(Sheet1!A3,Sheet2!$B1:$F$1,0)),Sheet2!$A:$A,">=" & Sheet1!A1,Sheet2!$A:$A,"<=" & Sheet1!A2)

Upvotes: 1

Related Questions