Search code examples
excelexcel-formulaarray-formulas

sumproduct to sum total days

In a spreadsheet I have two columns, 1st - date opened, 2nd - date closed.

What I'm trying to do is sum the total number of days where a file is not closed.

Using the following formulas, I can count the number of files that are still open;

=COUNTIFS(Data!L5:L6000,">0",Data!M5:M6000,"")

or

=SUMPRODUCT(--(Data!L5:L6000>0),(--(Data!M5:M6000="")))

Is there anyway to expand either of these to return the sum of the number of days from today, where the file is still open? Can this be done as an array formula perhaps?

Thanks

Solution

  • You can use =Sumproduct((Data!M5:M6000="")*(Today()-Data!L5:L6000))