Using a named range in an IFS formula's criteria

[nigelandrewfoster]

Hi,

I have an IFS formula thus:

=COUNTIFS(Orders!$V:$V,$K$4,Orders!$H:$H,I7,Orders!$G:$G,"unfulfilled",Orders!$E:$E,"<43566")

where 43566 is the serial number of a cut-off date. This date is however regularly changing.

I have created a named range, Order_Cuttoff_Date and put the date in that.

What is the correct way to amend the above formula, please? Because

=COUNTIFS(Orders!$V:$V,$K$4,Orders!$H:$H,I7,Orders!$G:$G,"unfulfilled",Orders!$E:$E,"<Order_Cuttoff_Date")

doesn't work.

I have searched Google but not come up with an answer yet so wondered if someone could enlighten me?

Thanks so much

Nigel

 

[RoryA]

You need to use:

"<" & Order_Cuttoff_Date

at the end.

 

[nigelandrewfoster]

Thank you again for your speed reply, Rory. Don't know what I'd do without you!

 

[tyija1995]

Instead of "<43566" (assuming this serial number is the named range of "Order_Cuttoff_Date" try replace it with:
"<"&Order_Cuttoff_Date
So your first formula becomes:
=COUNTIFS(Orders!$V:$V,$K$4,Orders!$H:$H,I7,Orders!$G:$G,"unfulfilled",Orders!$E:$E,"<"&Order_Cuttoff_Date)

 

[nigelandrewfoster]

Thanks tyija - I think that was a dead-heat. Appreciate your time. Love the signature