IF formula with filter getting a singular result

[ckdragon]

Hi Team,

I am trying to get a IF formula with a Filter function to work.

I have a table that I am referencing and I am trying to get the If formula to show a Yes/No result ONLY if the condition is met at least once.

The formula Is looking for a number that is greater than 2, if the other 2 filter parameters are met.

Sometimes there are multiple filter matches but all of them would = NO, and in which case I just want a single NO to show in the cell not one for each filter match (then all subsequent formulas will calculate as normal in the sheet)

On the chance though that there is even just 1 entry that meets the parameters then I need the result to display YES (so all subsequent formulas stop/other errors show etc)

Therefore, I don't need the result for every filter match, just an overall.

I can't use the text join function because this result feeds into other formulas which operates off the result.

Is there any better way to do this or to fix my below formula??

Hope that makes sense, thank you so much!

=IF(FILTER(Car1_TMR[Distance],(Car1_TMR[To]="Total Distance")*(Car1_TMR[Business Zone Trip]="Journey started and ended in a Business Zone"))>2,"YES","NO")

 

[Fluff]

Maybe

=IF(rows(FILTER(Car1_TMR[Distance],(Car1_TMR[To]="Total Distance")*(Car1_TMR[Business Zone Trip]="Journey started and ended in a Business Zone")))>2,"YES","NO")

 

[ckdragon]

Maybe

=IF(rows(FILTER(Car1_TMR[Distance],(Car1_TMR[To]="Total Distance")*(Car1_TMR[Business Zone Trip]="Journey started and ended in a Business Zone")))>2,"YES","NO")

Thank you.
Semi-worked...
only showed the FIRST result, not the specific preference result

order of preference
1. Yes - if it is 1 yes or many
2. No - only if there are no yes'

Any any suggestions would be amazing.

Thanks

 

[Fluff]

Try changing the 2 to 0

 

[ckdragon]

Try changing the 2 to 0

Okay getting there but, but I think maybe I didn't prove enough detail, because its not working exactly how I need if I change the data set.

essentially the table looks like the below.

Name	Rego num	Start	Stop	From	To	Distance	Business Zone Trip	Tolls	Zone start	Zone end
Car 1	Rego1	7:30:30 AM	7:57:26 AM	Base Stop	Stop 1	24.492			Base (Business)
Car 1	Rego1	8:13:23 AM	9:02:04 AM	Stop 1	Stop 2	76.323
Car 1	Rego1	9:55:01 AM	11:35:06 AM	Stop 2	Base Stop	101.266				Base (Business)
Car 1	Rego1		11:35:06 AM		Total Distance	202.081		Tolls
Car 1	Rego1	8:14:12 PM	8:15:08 PM	Base Stop	Base Stop	0.298	Journey started and ended in a Business zone		Base (Business)	Base (Business)
Car 1	Rego1		8:15:08 PM		Total Distance	0.298	Journey started and ended in a Business zone	Tolls
Car 1	Rego1	8:22:31 PM	9:06:18 PM	Base Stop	Base Stop	32.926	Journey started and ended in a Business zone		Base (Business)	Base (Business)
			9:06:18 PM		Total Distance	32.926	Journey started and ended in a Business zone	Tolls

and specifically I want the formula to show "YES" because the last row has a distance of greater than 2 (kms) AND was categorised in a "Business Zone".

But sometimes all the trips are under 2 and sometimes they are over 2.

The original formula I worked until there started being multiple of these kind of trips. essentially anything over 2kms is an issue for me and needs to flag "YES"

Hope that clarifies.

Thanks heaps!

 

[Fluff]

Ok, how about

=IF(COUNT(FILTER(ROW(Car1_TMR[Distance]),(Car1_TMR[Distance]>2)*(Car1_TMR[To]="Total Distance")*(Car1_TMR[Business Zone Trip]="Journey started and ended in a Business Zone"))),"YES","NO")

 

[ckdragon]

YOU DEAR SIR ARE AMAZING.

Works like a charm

THANK YOU SO SO VERY MUCH!

Ok, how about

=IF(COUNT(FILTER(ROW(Car1_TMR[Distance]),(Car1_TMR[Distance]>2)*(Car1_TMR[To]="Total Distance")*(Car1_TMR[Business Zone Trip]="Journey started and ended in a Business Zone"))),"YES","NO")

 

[Fluff]

You're welcome & thanks for the feedback.