Sumproduct

Abvlecxe

Board Regular
Joined
Sep 10, 2015
Messages
53
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
Try:

=SUMPRODUCT(B:B-A:A)+SUMPRODUCT(--(A:A=B:B),--(A:A&B:B<>""))
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,577
Messages
6,173,162
Members
452,503
Latest member
AM74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top