Search code examples
exceldateexcel-formulasumifs

SUMIF date in column B is less than date in column C

I have a list of values in Column A and want to sum these values where the date in Column B is less than Column C.

Example Example

In this example the correct value would be 2100.

I have previously had to complete this task with reference to a single date (e.g. the date contained in C3) and used the formula:

=SUMIF(B2:B11,"<="&C3,A2:A11)

However in this instance I need each date in B to be less than or equal to the date in C. I have tried:

=SUMIF(B2:B11,"<="&C2:C11,A2:A11)

But I get a value of 0. Any ideas?

Solution

  • Option A - Helper column

    The simplest approach for any scenario requiring working out on a row by row basis whether to do something or other - be it a sum, Lookup, etc - , is to use a 'helper' column alongside your data that uses a formula to work out whether it should or should not be included, typically returning 'True' if to be included.

    With your data layout, in column D add a calculation that works out the difference and therefore whether the row should be included, such as:

    =C2-B2 or =C2>B2 dragged down

    The SUMIFS calc then becomes =SUMIFS(A2:A11,D2:D11,">0") or, for the general case with more complex criteria, =SUMIFS(A2:A11,D2:D11,TRUE)

    This approach is excellent if you have a large number of complex criteria, perhaps varying by rows or perhaps requiring multiple AND() OR() and other logical evaluations (is a product ID x and is a customer ID y and is the value > z and is the days between purchase order being submitted and goods received more than 30 days) etc, to determine whether a row should be included in some further function.

    Option B - Array formula

    Alternatively, you can avoid the need for a working column by using an array formula that performs the comparisons in the formula. Under the data above this would be like:

    =SUM(IF(B2:B11<C2:C11,A2:A11,0)) entered with Ctrl + Shift + Enter