SUMIFS with Max

Hi All

Im trying to build a report for our operations team and have come up short on 1 column of data... I am trying to utilise SUMFS to establish a financial value based on criteria from multiple columns this i can do.

The formula sums the all values that meet the criteria (ie £7000) however i wish to only sum/return the max value (ie £3000)

So far i have

=MAX(SUMIFS('Sheet2'!E:E,'Sheet2'!A:A,C2,'Sheet2'!B:B,D2))


Destination Table

C D E F
Hotel l Room l Booking l Value
==================================
Hotel 1 l Deluxe Room l Period A l xxxxxx


Source Sheet2

A B C D
Hotel l Room l Booking l Value
==================================
Hotel 1 l Deluxe Room l Period A l £1000
Hotel 1 l Deluxe Room l Period A l £1000
Hotel 1 l Deluxe Room l Period B l £2000
Hotel 1 l Deluxe Room l Period C l £3000

Can you awesome folk at ExcelForum's help?

Many Thanks

Liam

sorry guys, i will expand the data set, perhaps that will make things clearer.

the attachment shows on the Source Sheet

the complex
the room types within that complex
the period of the stay
the number rooms of that room type
the total amount due if all rooms are booked out

because these rooms can been booked out at different periods lengths this provides different rates or that room type.

i am trying to establish the maximum potential rent should the room be booked out at the maximum rate.

in the case of complex 1 this is simple as there is only 1 period of stay, in the case of complex 2 there are 2 periods of stay at different total amounts

i wish to present the maximum of the 2 in my report, this report with us SUMIFS to validate Using the complex name and room type.

i hope this suits as a better description and apologies for missing the mark there

Thank you FlameRetired and Bo_Ry

To clarify based on the sample data attached earlier and using the Classic Studio as an example Room Type and for those searching for an answer!!!

Complex 2 has 2 Booking Types, each charging a different Rent (shown below)

31/08/2019 - 21/08/2020 51w - £518,160.00
07/09/2019 - 03/07/2020 43w - £500,200.00

If i was to use a SUMIFS it would sum the 2 together giving me a value of £1,018,360.00, this is not what im trying to achieve.

I need to show the max value.... in this case £518,160.00.

Bo_Ry, i tried your MAX, INDEX and MATCH, at first i looked at a full range and this returned #VALUE however once i changed it to look at a specific range i was not disappointed.

Thank you Bo_Ry