Pull a value from a row of cells if any cell in that row meets 2 condtions

[TommyWat]

Hello,

I'm trying to set up a sheet to manage my money and am having a little trouble getting a formula to work where I want a value to be pulled from the row if one of the dates in the row is between 2 set dates
I've been trying to use {=IF(AND($C$2:$W$2>=A9,$C$2:$W$2<A10)=TRUE,$B$2,"Not To Pay")} but only get the false result even when one of the values meet both conditions

Direct Debits

Mam Dept

£ 50.00

01/09/2020​

01/10/2020​

01/11/2020​

01/12/2020​

01/01/2021​

01/02/2021​

01/03/2021​

01/04/2021​

01/05/2021​

01/06/2021​

01/07/2021​

01/08/2021​

01/09/2021​

Mam B&L

£ 200.00

04/09/2020​

02/10/2020​

30/10/2020​

27/11/2020​

25/12/2020​

22/01/2021​

19/02/2021​

19/03/2021​

16/04/2021​

14/05/2021​

11/06/2021​

09/07/2021​

06/08/2021​

Gym

£ 24.99

24/09/2020​

24/10/2020​

24/11/2020​

24/12/2020​

24/01/2021​

24/02/2021​

24/03/2021​

24/04/2021​

24/05/2021​

24/06/2021​

24/07/2021​

24/08/2021​

24/09/2021​

Phone Insurance

£ 11.20

25/09/2020​

25/10/2020​

25/11/2020​

25/12/2020​

25/01/2021​

25/02/2021​

25/03/2021​

25/04/2021​

25/05/2021​

25/06/2021​

25/07/2021​

25/08/2021​

25/09/2021​

Phone Contract

£ 32.35

28/08/2020​

25/09/2020​

23/10/2020​

20/11/2020​

18/12/2020​

15/01/2021​

12/02/2021​

12/03/2021​

09/04/2021​

07/05/2021​

04/06/2021​

02/07/2021​

30/07/2021​

03/09/2020​

01/10/2020​

29/10/2020​

26/11/2020​

I'm using Excel 2016

 

[earthworm]

P

Hello,

I'm trying to set up a sheet to manage my money and am having a little trouble getting a formula to work where I want a value to be pulled from the row if one of the dates in the row is between 2 set dates
I've been trying to use {=IF(AND($C$2:$W$2>=A9,$C$2:$W$2<A10)=TRUE,$B$2,"Not To Pay")} but only get the false result even when one of the values meet both conditions

Direct Debits

Mam Dept

£ 50.00

01/09/2020​

01/10/2020​

01/11/2020​

01/12/2020​

01/01/2021​

01/02/2021​

01/03/2021​

01/04/2021​

01/05/2021​

01/06/2021​

01/07/2021​

01/08/2021​

01/09/2021​

Mam B&L

£ 200.00

04/09/2020​

02/10/2020​

30/10/2020​

27/11/2020​

25/12/2020​

22/01/2021​

19/02/2021​

19/03/2021​

16/04/2021​

14/05/2021​

11/06/2021​

09/07/2021​

06/08/2021​

Gym

£ 24.99

24/09/2020​

24/10/2020​

24/11/2020​

24/12/2020​

24/01/2021​

24/02/2021​

24/03/2021​

24/04/2021​

24/05/2021​

24/06/2021​

24/07/2021​

24/08/2021​

24/09/2021​

Phone Insurance

£ 11.20

25/09/2020​

25/10/2020​

25/11/2020​

25/12/2020​

25/01/2021​

25/02/2021​

25/03/2021​

25/04/2021​

25/05/2021​

25/06/2021​

25/07/2021​

25/08/2021​

25/09/2021​

Phone Contract

£ 32.35

28/08/2020​

25/09/2020​

23/10/2020​

20/11/2020​

18/12/2020​

15/01/2021​

12/02/2021​

12/03/2021​

09/04/2021​

07/05/2021​

04/06/2021​

02/07/2021​

30/07/2021​

03/09/2020​

01/10/2020​

29/10/2020​

26/11/2020​

I'm using Excel 2016

Please share excel file

 

[Peter_SSs]

Can you post your sample data again but include the expected result(s) and explain how you (manually) get those results?

 

[montecarlo2012]

Hello Tommy, can you upload something like

 

[montecarlo2012]

but only get the false result even when one of the values meet both conditions
maybe if you don't use IF like this example (only)

have a nice day

 

[montecarlo2012]

or maybe

 

[TommyWat]

Hello,

What I'm trying to do calculate the amount going out in direct debits for the period between my paydays.

one of the ways I'm looking at is having a formula check if any of the dates in the row match my criteria (between paydays) and output the value in the first collum,
I was trying to use an AND formula to check the dates, but I can't get it to work on multiple dates.
I was messing with arrays to try and get it to pull all the values that match my criteria at once.

the Calculation tab has 5 direct debits with the date they go out (unsure if there a better way to do this)
The paydays tab has the dates of paydays.

Here's a link to the excel file

My Cloud

files.mycloud.com

 

[Peter_SSs]

Here's a link to the excel file

The link does nothing for me. All I get is shown below. In any case you will tend to get faster help if you use XL2BB or copy/paste like you did in post 1 as many of the experienced helpers here choose not to download files from other sites or due to security restrictions at workplaces, are unable to download such files.

 

[earthworm]

lin

Hello,

What I'm trying to do calculate the amount going out in direct debits for the period between my paydays.

one of the ways I'm looking at is having a formula check if any of the dates in the row match my criteria (between paydays) and output the value in the first collum,
I was trying to use an AND formula to check the dates, but I can't get it to work on multiple dates.
I was messing with arrays to try and get it to pull all the values that match my criteria at once.

the Calculation tab has 5 direct debits with the date they go out (unsure if there a better way to do this)
The paydays tab has the dates of paydays.

Here's a link to the excel file

There is no file to download