Sum a column between a date range with a value cap

0
votes

I am trying to work with survey data and need to limit the rows used in a calculation based on a date range, and also the value in the column to sum needs to be capped at 2(reference cell to set limit). I don't want to use a helper column for simplicity of additional users that will be working with the file.

Assume dates are in column A, data is in column B, reference cell is F1 for cap, F2 for start date, and F3 for end date.

The array formula below is close what I need, but adding the date range has me stuck.

{=SUM(IF(Data!B:B<=F1,B:B,F1))}

1
excelexcel-formula

1 Answers

1
votes

Use MIN with SUMPRODUCT:

=SUMPRODUCT((A:A>=F2)*(A:A<=F3)*(MIN(F1,B:B)))