SUM values under MONTH/YEAR conditions

Question

I have the following table

╔════════════╦════════════╦════════╦════════╗
║ Booking    ║ Check-in   ║ Dif    ║ Status ║
╠════════════╬════════════╬════════╬════════╣
║ 25-09-2018 ║ 19-05-2019 ║ 234.15 ║ OK     ║
╠════════════╬════════════╬════════╬════════╣
║ 26-09-2018 ║ 05-05-2019 ║ 281.21 ║ OK     ║
╠════════════╬════════════╬════════╬════════╣
║ 26-09-2018 ║ 18-05-2019 ║ 264.11 ║ OK     ║
╠════════════╬════════════╬════════╬════════╣
║ 01-10-2018 ║ 19-06-2019 ║ 260.42 ║ CL     ║
╠════════════╬════════════╬════════╬════════╣
║ 01-10-2018 ║ 18-05-2019 ║ 228.16 ║ OK     ║
╚════════════╩════════════╩════════╩════════╝

I have been trying to create a sumproduct formula that will sum "Dif" if the month and year of check-in and booking date are the same under the condition that status is ok.

The output table I want to generate has the following form:

╔══════════════════╦═════╦══════╗
║ Booking/Check-in ║ May ║ June ║
╠══════════════════╬═════╬══════╣
║ September        ║     ║      ║
╠══════════════════╬═════╬══════╣
║ October          ║     ║      ║
╚══════════════════╩═════╩══════╝

The formula I used and received the error was:

=SUMPRODUCT(--('Data'!$D$2:$D$5="OK")*(MONTH('Data'!$A$2:$A$5)=MONTH($A2))*(YEAR('Data'!$A$2:$A$5)=YEAR($A2))*(MONTH('Data'!$B$2:$B$5)=MONTH(B$1))*(YEAR('Booking data'!$B$2:$B$5)=YEAR(B$1))*('Data'!$C$2:$C$5))

Any ideas?

Thank you in advance

Answer 1

Thank you all for your replies. I figured out what I should do. Please, find below the formula:

=SUMPRODUCT(--('Data'!$D$2:$D$5="OK"),(MONTH('Data'!$A$2:$A$5)=MONTH($A2))*(YEAR('Data'!$A$2:$A$5)=YEAR($A2))*(MONTH('Data'!$B$2:$B$5)=MONTH(B$1))*(YEAR('Data'!$B$2:$B$5)=YEAR(B$1))*'Data'!$C$2:$C$5)

Answer 2

Assuming that the month names in your output table are dates of the first of the month/year formatted to show as a full month name, you can use SUMIFS:

I converted the data table into a Table so as to be able to use structured references, which I find more understandable in complex formulas:

=SUMIFS(Table1[[Dif]:[Dif]],Table1[[Status]:[Status]],"OK",Table1[[Booking]:[Booking]],">="&$F2,Table1[[Booking]:[Booking]],"<=" &EOMONTH($F2,0),Table1[[Check-in]:[Check-in]],">="&G$1,Table1[[Check-in]:[Check-in]],"<="&EOMONTH(G$1,0))

We determine the date by ensuring that the Booking and/or Check-in dates occur in the same month as the output table. This will, of course, ensure that the month and year are the same.

The G$1 and $F2 addresses will depend on the location of the output table. But once you enter the formula in, for example G2, you can fill/drag it down and across and the references should automatically adjust.

Answer 3

I think the month name in your output table is actually a date formatted as full month name.

If you only want to sum by month name rather than actual date, here is one way of doing that:

=SUMPRODUCT((TEXT($A$2:$A$6,"mmmm")=$A9)*(TEXT($B$2:$B$6,"mmmm")=B$8)*($D$2:$D$6="OK")*($C$2:$C$6))

If you want to sum by actual date (taking into account the year component), then you can use the following formula:

=SUMPRODUCT((TEXT($A$2:$A$6,"MMM-YY")=TEXT($A13,"MMM-YY"))*(TEXT($B$2:$B$6,"MMM-YY")=TEXT(B$12,"MMM-YY"))*($D$2:$D$6="OK")*($C$2:$C$6))

Amend the cell references as needed. Cheers :)

The trick is to use TEXT function to convert the dates into a text format, in my example is "MMM-YY", so they can be compared easily without the need of comparing month and year component seperately. Sadly the EOMONTH function does not support array calculations otherwise the solution could be slightly simpler.

Answer 4

What you need for this is PIVOT TABLES:

Pivot Tables

I got this data, and the output is a Pivot Table:

Data:

Output:

My setup for my Pivot table:

1. Booking field to ROWS
2. Check-in field to COLUMNS
3. Both fields (Booking and Check-in) are grouped by MONTHS (check Group or ungroup data in a PivotTable)
4. Status field to FILTERS and set it up to show only OK values.
5. Dif field to VALUES
6. I also deactivated grand totals and subtotals (this is optional)

Hope this helps. In the output, June is not shown because there is no OK values in june to sum up