Problem with multiple IFS, IF and AND in long formula

[arendberg]

So I really hope someone can help me out here because this is driving me crazy! I need a formula to do the following.

I have a sheet with a lot of columns with different types of data. In a cell I need to calculate something based on multiple criteria.

If the date in column D is before 01-12-2023 and the text value in column L matches "Yes" make the 1st calculation. If the the date in column D is before 01-12-2023 and the text value in column L matches "No" make the 2nd calculation. If the date in column D is after 01-12-2023 and the text value in column L matches "Yes" make the 3rd calculation and finally when the date in D is after 01-12-2023 and the text value in column L is "No" make the 4th calculation.

This is already nested inside a larger IFS formula. I can get the the 1st calculation working but get stuck after that.

To make the story complete, these are the needed calculations:

1. F2/100*16,6*1,037
2. F2/100*13,6*1,037
3. (K2-G2)/100*16,6*1,038
4. (K2-G2)/100*13,6*1,038

Arosa Holiday 01-05-2023 <> 30-04-2024 data tm 06-03 v2.72.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Arrival	Departure	Res. No.	Created on	Distribution Channel	Logis	Resort Fee	Wasche	Reinigung	Extra's	Total	Preferred	Test Formula
2	8/31/23	9/3/23	10263	9/7/21	Booking.com	CHF 634.52	CHF 133.08	CHF 125.36	CHF 212.15	CHF 96.43	CHF 1,201.54	Yes	CHF 109.23
3	7/7/23	7/14/23	10851	12/16/21	Booking.com	CHF 1,128.25	CHF 81.00	CHF 72.32	CHF 154.29	CHF 0.00	CHF 1,435.87	No	CHF 210.75
4	8/31/23	9/4/23	10264	1/10/22	Airbnb	CHF 887.17	CHF 115.72	CHF 139.83	CHF 212.15	CHF 0.00	CHF 1,354.87	Yes	CHF 192.94
Sheet1

Cell Formulas
Range	Formula
M2:M4	M2	=IFERROR(IFS(E2="Booking.com",IF(AND(D2<DATEVALUE("1/12/2023"),L2="Yes"),(F2/100*16.6*1.037),((K2-G2)/100*15*1.037)),E2="Airbnb",SUM(F2+J2+H2+I2)/100*15*1.038,E2="Feratel",SUM(F2/100*15)/1.077,E2="Website",SUM(K2/100*3.5)),"0")

 

[bobsan42]

I have not tried to do the nesting since you have not described this logic.
Here is a formula based word-for-word on your description:

=IF(D2<DATEVALUE("1/12/2023"),IF(L2="Yes",F2/100*16.6*1.037,IF(L2="No",F2/100*13.6*1.037,"")),IF(D2>DATEVALUE("1/12/2023"),IF(L2="Yes",(K2-G2)/100*16.6*1.038,IF(L2="No",(K2-G2)/100*13.6*1.037,"")),""))

This one is not dealing with the case when D2 equals 1/12/2023.
Below is another one which includes 1/12/2023 in the second condition:

=IF(D2<DATEVALUE("1/12/2023"),IF(L2="Yes",F2/100*16.6*1.037,IF(L2="No",F2/100*13.6*1.037,"")),IF(L2="Yes",(K2-G2)/100*16.6*1.038,IF(L2="No",(K2-G2)/100*13.6*1.037,"")))

 

[arendberg]

Amazing thank you, this works perfectly!