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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
319
dshtesy
D
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
794
mst3kr
M
Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
216
Vintage79
Vintage79
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
356
dilshod_k
D
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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