Nested formula

[helenmeyer]

Hi all
I’ve been trying to do a formula to pull information fromthis table but I keep getting an error.
My formula is: =if(=COUNTIFS('25.2.19'!A:A,A4,'25.2.19'!$D:$D, $D$3, '25.2.19'!$F:$F, “Best offer”), <today)< font=""></today)<>

But I keep getting an error.
What I’m trying to do is a count of all the order status’ of“FDD” (Column S) for the specified contract number (column A) as long as columnF has “best offer” in it. I would likeit to also only the values where the date (column B) is less than 12 weeks thanthe date I run the report. I know I definitely don’t have the 12 weekspart right

Any help would be appreciated.

A	B	C	D	E	F	G
Contract​	Booked Date​	PO Raise Date​	Order Status​	Sup ID​	BSTOFR​	Due Date​
31D003	07-Aug-07​	03-Dec-07​	WIP	K0999	Best Offer	28-Feb-19​
31D003	01-Nov-07​	19-Feb-08​	COM	K0999	Best Offer	19-Jan-38​
31D003	27-Nov-07​	19-Feb-08​	WIP	K0999	Best Offer	28-Feb-19​
31D022	17-Jan-08​	21-Jan-08​	TEQ	K0656	Best Offer	19-Jan-38​
31D022	17-Jan-08​	21-Jan-08​	TEQ	K0656	Best Offer	19-Jan-38​
31D022	17-Jan-08​	21-Jan-08​	FDD	K0656	Best Offer	19-Jan-38​
31D022	17-Jan-08​	21-Jan-08​	TEQ	K0656	Best Offer	19-Jan-38​
31D022	17-Jan-08​	21-Jan-08​	COM	K0656		19-Jan-38​
31D022	17-Jan-08​	21-Jan-08​	TEQ	K0656	Best Offer	19-Jan-38​
31D022	17-Jan-08​	21-Jan-08​	TEQ	K0656	Best Offer	19-Jan-38​
31D022	17-Jan-08​	21-Jan-08​	FDD	K0656		19-Jan-38​

<tbody>
</tbody>

 

[Special-K99]

Re: Nested formula help Please

count of all the order status’ of“FDD” (Column S)

Why doesnt your formula contain a reference to column S? Did you mean column D, that contains FDDs ?

Based on this

"count of all the order status’ of“FDD” (Column S) for the specified contract number (column A) as long as columnF has “best offer” in it. I would likeit to also only the values where the date (column B) is less than 12 weeks thanthe date I run the report."

Try

=COUNTIFS('25.2.19'!S1:S1000,"FDD",'25.2.19'!F1:F1000,"Best Offer",'25.2.19'!B10:B1000,">="&TODAY(),'25.2.19'!B10:B1000,"<="&(TODAY()+84))

Change the red if necessary.

 

[etaf]

Re: Nested formula help Please

=if(=COUNTIFS('25.2.19'!A:A,A4,'25.2.19'!$D:$D, $D$3, '25.2.19'!$F:$F, “Best offer”),
remove the =
=if(COUNTIFS('25.2.19'!A:A,A4,'25.2.19'!$D:$D, $D$3, '25.2.19'!$F:$F, “Best offer”),

<today)< font=""></today)<>if the countifs is 0 = false 1 and above would be true

but i suspect you may have a < or > in the formula which this forum changes to html - just put spaces eitherside

 

[helenmeyer]

Re: Nested formula help Please

count of all the order status’ of“FDD” (Column S)


Try

=COUNTIFS('25.2.19'!S1:S1000,"FDD",'25.2.19'!F1:F1000,"Best Offer",'25.2.19'!B10:B1000,">="&TODAY(),'25.2.19'!B10:B1000,"<="&(TODAY()+84))

Change the red if necessary.

Thanks - this worked