Nested formula

H

helenmeyer

New Member
Joined
Mar 3, 2014
Messages
23
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.


[TABLE="width: 503"]
<tbody>[TR]
[TD="width: 68, bgcolor: transparent"]A
[/TD]
[TD="width: 98, bgcolor: transparent"]B
[/TD]
[TD="width: 98, bgcolor: transparent"]C
[/TD]
[TD="width: 111, bgcolor: transparent"]D
[/TD]
[TD="width: 98, bgcolor: transparent"]E
[/TD]
[TD="width: 98, bgcolor: transparent"]F
[/TD]
[TD="width: 98, bgcolor: transparent"]G
[/TD]
[/TR]
[TR]
[TD="width: 68"]
Contract​
[/TD]
[TD="width: 98"]
Booked Date​
[/TD]
[TD="width: 98"]
PO Raise Date​
[/TD]
[TD="width: 111"]
Order Status​
[/TD]
[TD="width: 98"]
Sup ID​
[/TD]
[TD="width: 98"]
BSTOFR​
[/TD]
[TD="width: 98"]
Due Date​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D003
[/TD]
[TD="width: 98, bgcolor: transparent"]
07-Aug-07​
[/TD]
[TD="width: 98, bgcolor: transparent"]
03-Dec-07​
[/TD]
[TD="width: 111, bgcolor: transparent"]WIP
[/TD]
[TD="width: 98, bgcolor: transparent"]K0999
[/TD]
[TD="width: 98, bgcolor: transparent"]Best Offer
[/TD]
[TD="width: 98, bgcolor: transparent"]
28-Feb-19​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D003
[/TD]
[TD="width: 98, bgcolor: transparent"]
01-Nov-07​
[/TD]
[TD="width: 98, bgcolor: transparent"]
19-Feb-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]COM
[/TD]
[TD="width: 98, bgcolor: transparent"]K0999
[/TD]
[TD="width: 98, bgcolor: transparent"]Best Offer
[/TD]
[TD="width: 98, bgcolor: transparent"]
19-Jan-38​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D003
[/TD]
[TD="width: 98, bgcolor: transparent"]
27-Nov-07​
[/TD]
[TD="width: 98, bgcolor: transparent"]
19-Feb-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]WIP
[/TD]
[TD="width: 98, bgcolor: transparent"]K0999
[/TD]
[TD="width: 98, bgcolor: transparent"]Best Offer
[/TD]
[TD="width: 98, bgcolor: transparent"]
28-Feb-19​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D022
[/TD]
[TD="width: 98, bgcolor: transparent"]
17-Jan-08​
[/TD]
[TD="width: 98, bgcolor: transparent"]
21-Jan-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]TEQ
[/TD]
[TD="width: 98, bgcolor: transparent"]K0656
[/TD]
[TD="width: 98, bgcolor: transparent"]Best Offer
[/TD]
[TD="width: 98, bgcolor: transparent"]
19-Jan-38​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D022
[/TD]
[TD="width: 98, bgcolor: transparent"]
17-Jan-08​
[/TD]
[TD="width: 98, bgcolor: transparent"]
21-Jan-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]TEQ
[/TD]
[TD="width: 98, bgcolor: transparent"]K0656
[/TD]
[TD="width: 98, bgcolor: transparent"]Best Offer
[/TD]
[TD="width: 98, bgcolor: transparent"]
19-Jan-38​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D022
[/TD]
[TD="width: 98, bgcolor: transparent"]
17-Jan-08​
[/TD]
[TD="width: 98, bgcolor: transparent"]
21-Jan-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]FDD
[/TD]
[TD="width: 98, bgcolor: transparent"]K0656
[/TD]
[TD="width: 98, bgcolor: transparent"]Best Offer
[/TD]
[TD="width: 98, bgcolor: transparent"]
19-Jan-38​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D022
[/TD]
[TD="width: 98, bgcolor: transparent"]
17-Jan-08​
[/TD]
[TD="width: 98, bgcolor: transparent"]
21-Jan-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]TEQ
[/TD]
[TD="width: 98, bgcolor: transparent"]K0656
[/TD]
[TD="width: 98, bgcolor: transparent"]Best Offer
[/TD]
[TD="width: 98, bgcolor: transparent"]
19-Jan-38​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D022
[/TD]
[TD="width: 98, bgcolor: transparent"]
17-Jan-08​
[/TD]
[TD="width: 98, bgcolor: transparent"]
21-Jan-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]COM
[/TD]
[TD="width: 98, bgcolor: transparent"]K0656
[/TD]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 98, bgcolor: transparent"]
19-Jan-38​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D022
[/TD]
[TD="width: 98, bgcolor: transparent"]
17-Jan-08​
[/TD]
[TD="width: 98, bgcolor: transparent"]
21-Jan-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]TEQ
[/TD]
[TD="width: 98, bgcolor: transparent"]K0656
[/TD]
[TD="width: 98, bgcolor: transparent"]Best Offer
[/TD]
[TD="width: 98, bgcolor: transparent"]
19-Jan-38​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D022
[/TD]
[TD="width: 98, bgcolor: transparent"]
17-Jan-08​
[/TD]
[TD="width: 98, bgcolor: transparent"]
21-Jan-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]TEQ
[/TD]
[TD="width: 98, bgcolor: transparent"]K0656
[/TD]
[TD="width: 98, bgcolor: transparent"]Best Offer
[/TD]
[TD="width: 98, bgcolor: transparent"]
19-Jan-38​
[/TD]
[/TR]
[TR]
[TD="width: 68, bgcolor: transparent"]31D022
[/TD]
[TD="width: 98, bgcolor: transparent"]
17-Jan-08​
[/TD]
[TD="width: 98, bgcolor: transparent"]
21-Jan-08​
[/TD]
[TD="width: 111, bgcolor: transparent"]FDD
[/TD]
[TD="width: 98, bgcolor: transparent"]K0656
[/TD]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 98, bgcolor: transparent"]
19-Jan-38​
[/TD]
[/TR]
</tbody>[/TABLE]





 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
Re: Nested formula help Please

Special-K99 said:
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.
Click to expand...


Thanks - this worked
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
882
mst3kr
M
Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
218
Vintage79
Vintage79
D
  • Solved
Formula to extract data for multiple critera
Replies
2
Views
63
Trebor76
T
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
366
dilshod_k
D

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top