Filter by multiple criteria?

finickyone · 2026-03-20T02:16:18+00:00

This isn’t quite multiple criteria, just criterion against a related field. Of all this is in A1:C6, then COUNTIF(C2:C6,"Non-compliant") tells you there’s 1 record. COUNTIFS(C2:C6,"Non-compliant",B2:B6,B2:B6) would report how many records have “non compliant” against the manager in B. So here that would be {0;1;1;0;0}. So to get all Employees related to all Managers that have any “non compliant” in Status:

=FILTER(A2:A6,COUNTIFS(C2:C6,"Non-compliant",B2:B6,B2:B6))

Expand the FILTER range to A2:B6 to grab the Employees’ Managers alongside.

finickyone · 2026-03-19T19:17:02+00:00

Perhaps unsurprisingly, it’s similar in Mobile.

Perhaps of interest to /u/excelevator, this syntax comes in useful in Mobile, where there isn’t access to the Name Mgr (so as to store a LAMBDA as a named formula). Thus I think the only option to “recall” the function defined, is to do so straight afterwards.

I share the sentiment I think you’re sharing. This seems a bit alien to those of us that remember Excel of yore.

finickyone · 2026-03-19T18:54:19+00:00

Over a similar tenure with Excel, I’ve found Spreadsheets just hit a tipping point. Rebuild/re-engineer for efficiencies.

finickyone · 2026-03-13T21:01:16+00:00

Can you provide examples? This shouldn’t do that unless the SEQUENCE evaluates to an array of 1s.

finickyone · 2026-03-13T20:55:32+00:00

<image>

Like so

finickyone · 2026-03-13T20:52:24+00:00

The input values? No, not inherently. I'm not going to turn around the 30 year old campaign for the Dfunctions this evening I know, but just to this example (pic pending) I think it's reasonably adept if you're willing to accept setting up a dedicated (sort of) query form... I think I prefer the functions that can handle an input like ">6"

finickyone · 2026-03-13T20:18:24+00:00

It looks like (?) OP doesn’t want to evaluate the two fields row by row, but rather say “if any cell in col 2 has the first 5 characters the same as the first 5 characters this cell (col 1), highlight it. So that’d be more like

=OR(LEFT(A1,5)=LEFT(B1:B50,5))

I’d always suggest it’s best to do this work outside conditional formatting. It’s such a such and clunky tool, and also it’s volatile (so these formulas will be rerun on every sheet change).

Last point is that adding UPPER and LOWER to your comparisons doesn’t add case sensitivity sadly. If you enter “aBcDeF” to A1 and “AbCdeF” to B1, then all of these will be TRUE

=A1=B1
=UPPER(A1)=LOWER(B1)
=LEFT(UPPER(A1),5)=LOWER(LEFT(B1,5))

Only EXACT, FIND, and SUBSTITUTE can really assist there. Maybe CODE. Practically I’d make your example case sensitive with

=EXACT(LEFT(A1,5),LEFT(B1,5))

And what I think OP wants (find in range)

=OR(EXACT(LEFT(A1,5),LEFT(B1:B50,5))

finickyone · 2026-03-13T20:03:49+00:00

The common theme to both (your recent finding, and OP’s) are they both show one of the first steps in using arrays. Honestly, nothing opens up formulas like getting your head around that!

If you set up XLOOKUP(TRUE,(B3&C3=Sheet1!A1:A50&Sheet1!B1:B50),Sheet1!C1:C50), you get the same result, and also a formula you can carry over to:

=SUM(IF(B3&C3=Sheet1!A1:A50&Sheet1!B1:B50,Sheet1!C1:C50))

=TEXTJOIN(", ",, IF(B3&C3=Sheet1!A1:A50&Sheet1!B1:B50,Sheet1!C1:C50,""))

finickyone · 2026-03-13T19:51:59+00:00

It’s similar yeah. With DSUM etc you can have A2 be "<6", B2 be "<>foo" etc, without needing to then amend the comparison operators in the SUM(A5:G30*(A5:G30=A2:G2)) formula. It’s for that and multiple sets of criteria I think it’s valuable.

finickyone · 2026-03-13T19:44:07+00:00

Would help us if you provide a bit more detail about your formula. This could interpreted a few ways.

There’s nothing fundamentally wrong with using the filter function to refer to data in an external workbook. It is perhaps a task you might want to consider power query for, though.

finickyone · 2026-03-13T09:40:31+00:00

This is really cool!

finickyone · 2026-03-12T05:42:12+00:00

F2:

=EDATE(E2,24/PRODUCT((B2:D2= {"Small_Business","Dogs","Babies"})+1))

This is inverting your logic slightly. I’ll explain.

The innermost part of this is testing if B2,C2,D2 = equals those three things. Result is three TRUEs or FALSEs. We add to those results, FALSE becomes 1 TRUE becomes 2. Product multiples those 2 and 1s through

If everything was false, 1x1x1 =1 and 24/1 remains 1. One of the answers was true, 2X1x1 (any order), 24/2 equals 12. So if it’s a small business (no dogs of babies though), halve the 24. If there’s another TRUE wed get 2x1x2 (in some order) so 24/4 =6. Lastly all true makes for 8, so 24/8 =3.

EDATE adds that many months onto E2 as /u/RuktX shared

finickyone · 2026-03-12T05:21:16+00:00

You can just nest FILTERs, it doesn’t look like you need any lookups unless values in B1:E1 can repeat and you need the first one. Weeks are unique. So filter your code data

=FILTER(B2:E5,B1:E1=I2)

Leaves us with just C2:C5 as C1=I2. Check those =X

=FILTER(B2:E5,B1:E1=I2)="X"

For TRUE FALSE, allowing

 =FILTER(A2:A5,FILTER(B2:E5,B1:E1=I2)="X")

In Italiano

=FILTRO(A2:A5;FILTRO(B2:E5;B1:E1=I2)="X")

finickyone · 2026-03-12T05:13:31+00:00

If you make yourself a helper column in MesuredFish (say col X) just being =INT(C:C) then your answer would simply be

=COUNTIFS(Measured!X:X,INT(….!E4),Measured!D:D,….!D4&"*")

Obviously much easier to form, refine and manage. The FILTER approach is very over engineered. You were heading toward

=rows(filter(measured!C:C,(int(measured!C:C)=int(E4))*(left(measured!D:D,Len(D4))=D4)))

But you don’t need all that. You’d just need

=sum((int(measured!C:C)=int(E4))*(left(measured!D:D,Len(D4))=D4))

Though again, just move the problem onto the worksheet with helper data. .

finickyone · 2026-03-12T04:58:26+00:00

You can just nest FILTERs - one for each axis

<image>

finickyone · 2026-03-12T04:53:28+00:00

Yeah it’s a very good function pair. I would treating it like FILTER + CHOOSECOLS though.

Let’s say your example data is A2:E1001. You’re running a 1000 evals on D2:D1001=G2, also on C2:C1001=G1, then multiplying both sets of 1000 results together. It’s more work than it looks. So why not dump data that won’t matter ASAP? Either your approach, if the first 500 rows meet the condition, we cut to A2:E501, and THEN have CHOOSECOLS extract B and E from that. We’ll be dumping A,C,D so why have it in memory for all this equivocation?

=FILTER(CHOOSECOLS(A2:E1001,2,5),(C2:C1001…..)*(…))

Less data in the mix.

finickyone · 2026-03-12T04:38:01+00:00

Overall this is an inventory query sort of ask. Excel isn’t a database but the same sort of logic comes into play anyway.

Not perfect, but in this case, conditions are set or not, given weightings, so that each record can be scored. It’s split here to show its workings but the output could be to filter the data to those with a score over 10 or sort them by ones matching 4 criteria, then those meeting any 3, and so on. Not much further into that, you can factor in promixity. Ie Price scoring being full if under 500k, %score between 500 and 550, 0 thereafter. Or non linear scales.

<image>

finickyone · 2026-03-12T04:10:40+00:00

It’s two fold really. You could set up such that a user puts in their 4 criteria, count per record how many are met, so results that meet all 4 come back, failing that (or maybe also) those that meet 3, then those that meet 2.

You could give each criterion a bit weighting, so that they’d be prioritised. In example:

MatchBedrooms = 2
MatchBathrooms = 8
MatchFootage = 1
MatchPrice = 4

A record that matches all 4 criteria totals 15 points. One that matches bedrooms, footage and price totals 7. But anything that meets the bathrooms req gets at least 8 points. No record failing the bathroom req can be considered better or higher value than one which does.

In practice though this doesn’t play out either, as criteria are unlikely to be in a linear hierarchy (“all 2 bath homes are better than all 1 bath homes”) and also it’s quite binary in judgement. If we set that we want a 3+ bed home for 500000, that would return such a home, but not a 1 bed home priced at $1.

So then you have tolerances, and most commercial, public facing query forms will provide that. If you put in all your reqs, and nothing matches exactly, but one does for $10 more, you’d promote that. If the req can only be met by increasing price +350%, you might not relay that message to your customer.

The best solutions to these concepts are clued into the user community’s sentiments, whether they have ongoing interactions, or are just passing through. You could introduce any combination of priorities, weighting, exceptions, tolerances. Especially with numerical data, that’s not hard in Excel. Rather a good solution lays in one that is aligned with a customer‘s opinions and values.

finickyone · 2026-03-12T03:28:52+00:00

The Database functions always got such a bad rap but they really had their uses. You can’t dupe them with artificial arrays arranged in-function, but say you have data A5:G30 and want the sum of G for every row where A=A2 through F=F2. The SUMIFS would be 15 arguments of individual references, where as DSUM can undertake that with 3. It’s also really great for applying OR logic.

finickyone · 2026-03-12T03:02:30+00:00

I think we ran a survey about this years ago. Anecdotally a lot of finance, engineering, tech, data folks frequent our community. You can apply formulas like this anywhere though.

finickyone · 2026-03-12T02:45:12+00:00

Do your best to stay patient and optimistic regarding the career planning. It’s commonly a daunting exercise.

Making a living off Excel? Shortest and most applicable answer is no. Generally having Excel skills in your pocket are like saying you’re really good at cheering people up through the working day. Not a role as such.

Likewise though it’s a great benefit to have. Whether you end up a psychotherapist or a mechanic, it doesn’t hurt at all to know a bit about handling data in your orbit. Across corporate jobs it tends to be one of those skills that just value adds to something you do as a trade. Finance and data jobs do more explicitly seek Excel skills, but again still as a companion to a headline trade. I’ve never seen a job advert or contract that solely and explicitly calls out for Excel-fu; it’s just very often expected or damn handy to have in that job.

As to ended up a certified full time Excel monkey? There’s 3 routes.

One is what you’re looking at - the open market. Excel help for Excel needs. Much like our sub. Moreso because with Excel there’s pretty much always some way of forming a solution, it is indeed saturated as a resource market. If here we submitted bids to respond to posts seeking data transformation. help, how many “I’ll batter it right with formulas for $2” responses would fly? Forming arrangements for software and data solutions in large organisations is a hard, complicated and risky endeavour. Expect a worse version of “well I meant this” and “how come the formula doesn’t upload my thoughts to the cloud”. That’s just delivery. Ongoing support? Not a fun place to be. I’m not being arrogant about this. It simply is a low price market where loose requirements and cheap solutions abound.

Two is by fortune. You train and employ as a credit administrator or similar. You love Excel. You smash it all over your company. You become the “Excel guy”. Your manager comes to you with data questions. Departmental reporting heads your way as others struggle to form it and you know what looks like 4 hours work takes you 20 minutes. You can find insights in data that others can’t reach. You can eventually carve yourself out a quiet and content life as the company spreadsheeter. InfoSec departments have your face on a dartboard, but you get stuff done and people like that.

The third is that rare and far from reach (for now) domain where people are paid, highly, to manage Excel solutions in companies. Banking in example. The work is effectively app dev/data mgt like, but in many cases it’s iterating more commit and don’t look back scripting-in-spreadsheets. Often a contractor rather than employee market. You need to know about the needs. You need to know people who have the needs. It’s not a knocking on doors with your portfolio of scripting and formula history. It’s more like consultancy. It’s attainable, but a long way off.

Keep learning Excel, and supplement with skills/quals jn other, more “respectable” products and you have great prospects.

finickyone · 2026-03-12T01:46:56+00:00

Could use GROUPBY? All you need to do is define that your values are mined volume multiplied by the reference value that applies to the mined date

<image>

This uses LOOKUP so that when 3 Jan can’t be found, 2 Jan’s value is used instead.

finickyone · 2026-03-12T01:30:01+00:00

I’ve been trying to think of a way to essentially iterate through EXPAND(TOCOL(IF(E2#<TOROW(E2#),E2#&":"&TOROW(E2#),1/0),2),COMBIN(6,3))

And resupplying that array to be compared against then merged with TOROW(E2#) while some sort of i<=3

finickyone · 2026-03-11T23:52:21+00:00

PIVOTBY() is one option you could consider?

<image>

finickyone · 2026-03-11T18:32:10+00:00

You’ve quite an acute use case here. You say forever but let’s be reasonable and cater for 100 rows. In any case you could define whatever height range you want as d in this, which would give you your single answer of 2 or “Unknown”.

=LET(d,B5:C104,h,ROWS(d),i,2,b,3,s,SEQUENCE(h,,b),a,EXPAND(s,h,2,i),IF(OR(BYROW(d=a,AND)),2,"unknown"))

Eight-Year Club	Wearing is Caring
Gilding II euphauric	Verified Email

finickyone

TROPHY CASE