Search code examples
excelexcel-formulasumifs

How can I put together a SUMIFS in Excel to include an OR?

I'm trying to put together a calendar of regular payments which will have a date begun, day the money is paid and the date cancelled (if there is one), however I'm struggling with setting up my SUMIFS function. I've set up an example here.

What I'm trying to do is add the paid value on the day the payment is made, if the date started is less than the date, and only if the date cancelled is empty or the date is before the date cancelled.

List of payments:

      A            B          C
1  Payment | Begun      | Cancelled
  ---------+------------+------------
2  £5.00   | 01/01/2016 |
3  £9.00   | 04/01/2016 | 01/02/2016

Calendar (that I'd expect):

      E          F 
1  Date     | Payment   
  ----------+---------
2  01/01/16 | £5.00
3  02/01/16 | £0.00
4  03/01/16 | £0.00
5  04/01/16 | £9.00
...
33 01/02/16 | £5.00
34 02/02/16 | £0.00
35 03/02/16 | £0.00
36 04/02/16 | £0.00

So in this example: £5 should be added on 01/01/16 and 01/02/16, while £9 should be added on 04/01/16 only.

So far in the F column I have: 

=SUMIFS($A$2:$A$3, DAY($B$2:$B$3), "="&DAY(E2), $B$2:$B$3, "<="&E2, $C$2:$C$3, ">="&E2)
Solution

  • This Array formula in F2 should do what you want:

    =SUM(IF((DAY($B$2:$B$3)=DAY(E2))*(E2>=$B$2:$B$3)*(E2<=$C$2:$C$3),$A$2:$A$3))+SUM(IF((DAY($B$2:$B$3)=DAY(E2))*(E2>=$B$2:$B$3)*(""=$C$2:$C$3),$A$2:$A$3))

    It is an Array formula and must be confirmed with Ctrl-Shift-Enter. Then copied down.

    The 'OR' in Array formulas must use a +. If all you had were AND the * would work.

    enter image description here

    I assume you are going to be adding dates to columns A:C, you can change all the small absolute ranges to the desired ranges and it will work for multiples.

    Do forgive our backwards dates.