Sumproduct

[Abvlecxe]

Hello, I am trying to use Sumproduct to calculate the difference between two dates per row, using the following:


Column A = From Date
Column B = To Date


I have tried both of the following which do not work:



Sumproduct(B:B-A:A)
Sumproduct((--B:B)-(--(A:A)))



Any help would be appreciated

 

[Jon von der Heyden]

Hi

What does the first formula give you? I think it should work.

 

[Abvlecxe]

Actually i've checked this line by line and it works to an extent, where i now see it falls over is that where you subtract the same date it returns 0 whereas i need the formula to count this as a day.


So if in column A and b is the same date e.g. 01/01/2018 the current formula sumproduct(B:B-A:A) returns 0 but I need this to count as one day, any ideas?

Hi

What does the first formula give you? I think it should work.

 

[Jon von der Heyden]

Try:

=SUMPRODUCT(B:B-A:A)+SUMPRODUCT(--(A:A=B:B),--(A:A&B:B<>""))

 

[Jon von der Heyden]

Although you can simplify if you use avoid using whole column and if your data is in a contiguous range.

=SUMPRODUCT(B1:B100-A1:A100)+SUMPRODUCT(--(A1:A100=B1:B100))

The whole column approach will be quite slow to calculate.

 

[Abvlecxe]

Hi, that is 99% there. It now gives 1 where the dates match which is good but where the dates don't match i also need it to add 1:

E.g. column a = 01/01/2018
column B = 08/01/2018


The current formula returns 7 but in reality the formula needs to count the start day as well to return 8


Thanks

Although you can simplify if you use avoid using whole column and if your data is in a contiguous range.

=SUMPRODUCT(B1:B100-A1:A100)+SUMPRODUCT(--(A1:A100=B1:B100))

The whole column approach will be quite slow to calculate.

 

[Aladin Akyurek]

Perhaps just this...

=SUM(B:B)-SUM(A:A)+1