sumproduct array formula not working

Question

hi,

i would like to create a formula where i calculate the total days between 2 dates per row and provide one total number. below the formula i use

=SUMPRODUCT(MAX(0; MIN(IF(($C$2:$C$7 > DATE(2024; 3; 31)) + ISBLANK($C$2:$C$7); DATE(2024; 3; 31); IF(($C$2:$C$7 < DATE(2024; 3; 1));0;$C$2:$C$7)); DATE(2024; 3; 31)) - MAX(IF($B$2:$B$7 < DATE(2024; 3; 1); DATE(2024; 3; 1);IF($B$2:$B$7 > DATE(2024; 3; 31);0; $B$2:$B$7)); DATE(2024; 3; 1)) + 1))

column B is the "start date" and column C the "end date" and i want the total days in March. should the end date in column C be blank, it needs to use the end of march as end date
below is the data im using

Start date	End Date
01/02/2024	28/02/2024
07/02/2024
15/02/2024	17/03/2024
01/03/2024	06/03/2024
12/03/2024
05/04/2024	07/05/2024

appreciate the support here

gr

micky

Answer 1

MIN and MAX do not return an array of values, but a single value. You want to sum an array of values.

Answer 2

Hi,

In cell B12, enter this formula

=SUM(IF(B2:B7="",DATE(2024,3,31),B2:B7)-A2:A7+1)

Hope this helps.

Answer 3

this worked perfectly! thanks
i just wonder why my formula didnt work

Answer 4

If you have Excel 2019 or older:

=SUM(IF(IF((C2:C7>DATE(2024, 3, 31))+(C2:C7=""), DATE(2024, 3, 31), C2:C7)-IF(B2:B7<DATE(2024, 3,1), DATE(2024, 3, 1), B2:B7)<0, 0, IF((C2:C7>DATE(2024, 3, 31))+(C2:C7=""), DATE(2024, 3, 31), C2:C7)-IF(B2:B7<DATE(2024, 3,1), DATE(2024, 3, 1), B2:B7)))

Confirm the formula by pressing Ctrl+Shift+Enter.

If you have a newer version:

=LET(first, DATE(2024, 3, 1), start, IF(B2:B7<first, first, B2:B7), last, DATE(2024, 3, 31), end, IF((C2:C7>last)+(C2:C7=""), last, C2:C7), difference, end-start, SUM(IF(difference<0, 0, difference)))

You don't need Ctrl+Shift+Enter for this.