Based on Dates, how to know the discount if Yes or No column?

[jamiguel77]

Based on Dates, how to know the discount if Yes or No column?

Desde	Hasta	Yes	No
27/02/2011	26/02/2013	13%	10%
26/02/2013	26/02/2015	11%	8%
26/02/2015	25/02/2017	9%	7%
25/02/2017	25/02/2019	7%	4%
25/02/2019	more…	1%	2%
15/03/2016	Yes
05/04/2014	No
24/04/2012	No
11/05/2019	Yes
15/04/2012	No

the first cell(C11) would be the answer 9%


thanks

 

[jasonb75]

As long as there is no overlap with the date ranges. If the date is an exact match then it will return the percentage that starts on that date, not the one that ends. For example, 26/02/2013 will return 11% for Yes, not 13% (although technically, both are correct based on the table).

It would be better to either decrease the dates in column B by 1 day, or increase those in column A by 1 day so that they don't clash.

Book1
	A	B	C	D
3	Desde	Hasta	Yes	No
4	27-Feb-11	26-Feb-13	13%	10%
5	26-Feb-13	26-Feb-15	11%	8%
6	26-Feb-15	25-Feb-17	9%	7%
7	25-Feb-17	25-Feb-19	7%	4%
8	25-Feb-19	03-Jun-21	1%	2%
9
10
11	15-Mar-16	Yes	9%
12	05-Apr-14	No	8%
13	24-Apr-12	No	10%
14	11-May-19	Yes	1%
15	15-Apr-12	No	10%
Sheet2

Cell Formulas
Range	Formula
C11:C15	C11	=SUMIFS(IF(B11="Yes",$C$4:$C$8,$D$4:$D$8),$A$4:$A$8,"<="&A11,$B$4:$B$8,">"&A11)

 

[kweaver]

I wrote a slightly different formula after adjusting my test data for US dates:

Book6
	A	B	C	D	E	F	G	H	I
3	Desde	Hasta	Yes	No				Yes	No
4	27/02/2011	26/02/2013	13%	10%		2/27/2011	2/26/2013	13%	10%
5	26/02/2013	26/02/2015	11%	8%		2/27/2013	2/26/2015	11%	8%
6	26/02/2015	25/02/2017	9%	7%		2/27/2015	2/25/2017	9%	7%
7	25/02/2017	25/02/2019	7%	4%		2/26/2017	2/25/2019	7%	4%
8	25/02/2019	more…	1%	2%		2/26/2019	2/25/2021	1%	2%
9
10								PERCENT
11	15/03/2016	Yes				3/15/2016	Yes	9%
12	05/04/2014	No				4/5/2014	No	8%
13	24/04/2012	No				4/24/2012	No	10%
14	11/05/2019	Yes				5/11/2019	Yes	1%
15	15/04/2012	No				4/15/2012	No	10%
Sheet4

Cell Formulas
Range	Formula
H11:H15	H11	=INDEX($H$4:$I$8,MATCH(F11,$F$4:$F$8),CHOOSE(1+("No"=G11),1,2))

 

[jamiguel77]

thanks both