Problem getting COUNTIFS formula working

[Harry12345]

I'm trying to write a formula that will count all dates between Monday and Friday of this week and the value in the adjacent column in not equal to "Fully Received". I know what I want to do and a general idea of the formula I need to use, but I can't seem to get the syntax right to get it to work. The below formula achieves what I want.

=COUNTIFS('Purchase Orders'!D:D,">=19/05/2014",'Purchase Orders'!D:D,"<=23/05/2014",'Purchase Orders'!E:E,"<> Fully Received")

The problem is that I need to replace the date strings with a formula that automatically works out the date of Monday and Friday of the current week, I have this working but when I insert into the above formula I get a formula error, below is what I want to achieve, but it give me a formula error.

=COUNTIFS('Purchase Orders'!D:D,>=(=TODAY()-WEEKDAY(TODAY()) + 2),'Purchase Orders'!D:D,<=(=TODAY() - WEEKDAY(TODAY()) + 6),'Purchase Orders'!E:E,"<> Fully Received")

I also tried replacing the TODAY formula bit with a reference to a cell with the same formula but I still get a formula error, I have tried various combinations of using and not using '' and "" and () but I just can't seem to get it to work.

 

[konew1]

Try this. Test please

=COUNTIFS('purchase orders'!D:D,">="&TODAY()-WEEKDAY(TODAY()) + 2,'purchase orders'!D:D,"< ="&TODAY() - WEEKDAY(TODAY()) + 6,'purchase orders'!E:E,"< > Fully Received")

Remove extra spaces I entered after each "<" character

 

[Harry12345]

Try this. Test please

=COUNTIFS('purchase orders'!D:D,">="&TODAY()-WEEKDAY(TODAY()) + 2,'purchase orders'!D:D,"< ="&TODAY() - WEEKDAY(TODAY()) + 6,'purchase orders'!E:E,"< > Fully Received")

Remove extra spaces I entered after each "<" character

Thanks for your reply, I entered your formula, with spaces removed and I didn't get a formula error but the result was 0 when it should be 25. I also tried replacing the "&TODAY()...." bits with &S11 and &S12 (cells which contain the same formula) but it still gave me 0 as the result.

 

[konew1]

I had a surplus space in the last condition. Either of these should do it

=COUNTIFS(D:D,">="&TODAY()-WEEKDAY(TODAY()) + 2,D:D,"<="&TODAY() - WEEKDAY(TODAY()) + 6,E:E,"<>"&"Fully Received")
=COUNTIFS(D:D,">="&TODAY()-WEEKDAY(TODAY()) + 2,D:D,"<="&TODAY() - WEEKDAY(TODAY()) + 6,E:E,"<>Fully Received")

 

[Harry12345]

I had a surplus space in the last condition. Either of these should do it

=COUNTIFS(D:D,">="&TODAY()-WEEKDAY(TODAY()) + 2,D:D,"<="&TODAY() - WEEKDAY(TODAY()) + 6,E:E,"<>"&"Fully Received")
=COUNTIFS(D:D,">="&TODAY()-WEEKDAY(TODAY()) + 2,D:D,"<="&TODAY() - WEEKDAY(TODAY()) + 6,E:E,"<>Fully Received")

Cheers mate, worked like a charm, next time I'll know to use the & sign as well.

 

[konew1]

Great. Thanks for the feedback