Filter/Take formula

[erniepoe]

Hi All,

I have the following formula (provided to me earlier by a helpful member here), that works great. I had added the clause that I would only like to sum column P if column T contained "Professional Services," but unfortunately that doesnt end up with the correct result.

Any idea how I can update the formula so that it works correctly? Thanks so much.


=IFERROR(LET(f,FILTER('Bookings - Jan 2024'!$B:$P,('Bookings - Jan 2024'!$B:$B=O$6)+('Bookings - Jan 2024'!$B:$B="Subtotal")+('Bookings - Jan 2024'!$T:$T="*Professional Services*")),INDEX(TAKE(f,,-1),XMATCH(O$6,TAKE(f,,1))+1)),0)

 

[RoryA]

+ is the equivalent of OR in your filter criteria so your results will include anything that matches at least one of the three criteria. To combine an OR on column B with AND on column T you need to multiply the col T filter:

=IFERROR(LET(f,FILTER('Bookings - Jan 2024'!$B:$P,(('Bookings - Jan 2024'!$B:$B=O$6)+('Bookings - Jan 2024'!$B:$B="Subtotal"))*('Bookings - Jan 2024'!$T:$T="*Professional Services*")),INDEX(TAKE(f,,-1),XMATCH(O$6,TAKE(f,,1))+1)),0)

 

[erniepoe]

ah, i see. thank you Rory!

 

[Scott Huish]

The reason for this is that there are numeric equivalents to TRUE and FALSE. Zero is FALSE, anything non-zero number is TRUE.
Doing math (on the spreadsheet, VBA is slightly different) on Boolean (TRUE/FALSE) expressions coerces them to 0 (FALSE) and 1 (TRUE).
So if you are adding values, if anyone is correct, it will be a non-zero number, thus TRUE. This makes it equivalent to OR (FALSE+TRUE=TRUE)
If you are multiplying values, multiplying by any FALSE (Zero) value will render the whole expression Zero, equating to FALSE. This makes it equivalent to AND (FALSE*TRUE=FALSE)

 

[erniepoe]

so the above formula from Rory actually returns a 0 result. I tried changing the original range to include column T, as well as having an exact match on the professional services clause - ("SedonaOffice (Inside) - Professional Services Only") - but no dice.

Any ideas what might be the problem?

 

[RoryA]

I didn't look very closely at your conditions. Are you attempting to match where column T contains "Professional Services"? The = operator doesn't use wildcards so you are currently checking where column T is equal to the literal text "*Professional Services*" including asterisks. I suspect you want:

=IFERROR(LET(f,FILTER('Bookings - Jan 2024'!$B:$P,(('Bookings - Jan 2024'!$B:$B=O$6)+('Bookings - Jan 2024'!$B:$B="Subtotal"))*(ISNUMBER(SEARCH("Professional Services",'Bookings - Jan 2024'!$T:$T)))),INDEX(TAKE(f,,-1),XMATCH(O$6,TAKE(f,,1))+1)),0)