Share via

SUMPRODUCT returns zero using MIN MAX EOMONTH with multiple rows

Anonymous
2025-03-02T09:26:43+00:00

Hi,

I have a sheet which contains arrival dates in column A and departure dates in column B.

In cell column D, I have dates representing the first day of the month. For each month, I want to calculate the number of nights stayed based on all date ranges in columns A and B for the month as specified in column D. In the example below, June should be 33 and July 43.

Arrival Departure Date Nights in period
08/06/2024 08/07/2024 01/06/2024 0.00
03/06/2024 07/06/2024 01/07/2024 0.00
25/05/2024 26/05/2024
24/06/2024 27/06/2024
17/06/2024 21/06/2024
01/07/2024 05/07/2024
25/07/2024 29/07/2024
19/07/2024 21/07/2024
21/07/2024 25/07/2024
05/07/2024 26/07/2024
30/07/2024 03/08/2024

All date cells are formatted as Excel 'Date' with the UK display format of dd/mm/yyyy.

The following formula returns zero.

=SUMPRODUCT(MAX(0, MIN(EOMONTH(D2,0), B:B) - MAX(D2, A:A)))

It returns the correct result when there is only one row of dates (E.G. delete all data from row 3 down), although I do need to correct this at some point as I was expecting 23 and 7 (separate issue).

Arrival Departure Date Nights in period
08/06/2024 08/07/2024 01/06/2024 22.00
01/07/2024 7.00

The component parts return their expected values (MIN, MAX, EOMONTH). Even with only two rows of date where there are no overlaps, I still get zero.

Arrival Departure Date Nights in period
08/06/2024 08/07/2024 01/06/2024 0.00
03/06/2024 07/06/2024 01/07/2024 0.00

I've also tried constraining the range to just those with dates (E.G. B2:B12 rather than B:B) which does not work either.

Can someone please help me work out why this fails for more than one row?

Thanks

Carl Gilbert

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2025-03-09T09:59:52+00:00

Thanks for everyone's help on this.

@HansV - your formula worked well however I had to add in a day to include the end of the month in some instances, as identified by @Rand2201.

This is the formula I now have which works:

Thanks for your suggestion Ashish, however I do not know Power Query.

Was this answer helpful?

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2025-03-02T12:31:35+00:00

The problem is that MAX and MIN return a single value, not an array of values.

In E2: see screenshot, The brain-dead forum refuses to post formulas with > or < correctly,

Adjust the ranges as needed; I wouldn't use entire columns for better performance.

Was this answer helpful?

0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-03-09T15:38:03+00:00

    You may have one more thing to sort out; take an example of someone arriving on the last day of June and leaving on the first day of July, that is one night. Do you want that one night to appear in June's result or July's result, or both?

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 102.1K Reputation points Volunteer Moderator
    2025-03-02T23:09:04+00:00

    Hi,

    I used Power Query to solve the problem. This code works

    let 

    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Added Custom" = Table.AddColumn(Source, "Date", each {Number.From([Arrival])..Number.From([Departure])-1}), 

#"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"), 

#"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"Arrival", "Departure"}), 

#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}), 

#"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text), 

#"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type), 

#"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Month Name"}, {{"Night stays", each Table.RowCount(\_), Int64.Type}}), 

#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Year", Order.Ascending}, {"Month Name", Order.Ascending}})

    in 

    #"Sorted Rows"

    Hope this helps.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-03-02T20:36:37+00:00

    > ...  I want to calculate the number of nights stayed

    > ... June should be 33 and July 43.

    Are you sure it's not 34 & 44

    < Deleted...>

    Image

    Was this answer helpful?

    0 comments