What's the equivalent to a sumifs formula but instead of #s I want to bring in names. For example, i want to return a client name but only if it meets two criteria.

like
Posting as :
works at
You are currently posting as works at

Make a key column by concatenating the two criteria columns and look up with that. Kind of ugly, but worls

like

If you needed it to be a bit more aesthetically pleasing you could write a nested if statement in your key column returning “Yes” if the data meets both criteria, or “No” if not.

like

=ifs allows you to have multiple criteria

like

Google “index match array formula”, should do the trick. Otherwise have a service column that combines the different criteria appropriately, then hide it away.

like

So you’re looking for a multi-criteria lookup formula?

like

There is also filter formula if you want all the matches to be returned.

like

You can do textjoin(delimiter,filter array if you want it in a single cell

like

The filter function is the only answer you’re looking for. And if you want to sort the listing in a certain order you would add the sort function followed by filter function

like

I would suggest a textjoin(delimiter, sort(unique(filter... to put in single cell too

like

Filter function, but if you’re bored and wanna learn some black magic excel google an index match array function

funny

Yes, but index match match won't work bc the criteria is listed down each column.

Concatenate the lookup criteria in the columns to create a single, unique identifier then use a lookup function like you normally would.

like

Yeah it’s definitely not sumif. I would concatenate a key as A1 said

Iferror and index match

Xlookup

If(and) or vlookup.

That is an index match or lookup(v, x etc)

You can combine vlookup and choose; but it’s pretty expensive from a resource perspective.

The new xlookup.

The answer is what I term a "multi-match":

=INDEX([return column],MATCH(1,([search column 1]=[criteria 1])*([search column 2]=[criteria 2]),0)

Enter as array formula (press 'Ctrl+Shift+Enter')

Note you can add as many criteria as you want in the MATCH by adding more instances of "...*([search column #]=[criteria #])"

Related Posts

Have any of you taken "low bono" clients while working at a firm? I want to take on a low bono client or two but not sure who would get the money. This is a contract opportunity through a non profit so I'm assuming a 1099 will be issued. The per hour rate is significantly less than my billable rate. I would (obviously) like the money but my real objective is just getting experience/networking since I recently moved to a new state and don't have clients of my own.

like

Is there a point in reaching out to HR if I am being mentally tortured in the project. My project manager has cancelled my leaves which I planned 2 months in advance and approved for the person who is very close to him (Chaatu). Will the HR help me here or start giving me gyaan about company policies.

KGS, Consulting

like

Hi everyone, looking for referrals into pharma/biotech. Currently in the CRO/vendor world and have 6 years of experience in clinical trial management, project management and site qualification with experience in phases 2-4.

like

Fellow 🐠🐠, beginning work at new firm on a legacy project with others who have much more experience than I. Any tips for success/not embarrassing myself?

Hi folkes,
Any referral for business project manager at Microsoft?

like

As an M Is it better to take a project with a bad SM but potentially easy work. Or a harder project working directly with a good PPMD with a tough client and tough project?

like

After spending a decade in research and consulting, i got an offer from Grant Thornton which is more of business reporting and supporting client teams to manage clients, improving data processes/quality and all. No client interaction on the face of it. Any thoughts on merits on switching here?

Dear recruiters/hiring managers, a Project Manager is NOT an entry level position. If you want 3 years experience in the industry, it is NOT an entry level position.
Are you just skipping that detail and default to entry level? Why would that be overlooked? I have 7 years experience in project coordination and 3D production coordination: if the job is ‘entry level’ I figure they pay peanuts for someone who has a lot of responsibility on their shoulders. Am I too quick to write these off?

like

How is citi bank project in TCS Pune

like

Anyone out there ever been assigned to an engagement where you don't agree with the client's business practices? Ex: Tobacco, big pharma, or unethical strip mining...

like

My client is in Minnesota and let’s just say the project hasn’t gone the greatest. I have heard him say at least half of these. 😬

Post Photo
likefunnysmarthelpful

Is standard chartered client at Virtusa is good for java developer

How is WLB at Tech Mahindra SBI Belapur client (on site), Can anyone help me with feedback?

like

How to request for change in DOJ? i want to postpone it by 3 weeks for taking care of some personal work. Anyone who has done it in the past pls help.. PS - I am not tagged to any project yet so i hope it can be done without any issues

like

What to say when a project manager thinks that they alongside stakeholders should determine the priority of your roadmap and backlog? 🤯

like

Hi folks, people who are trying to relocate to new locations like Chennai Pune Kolkata, are there any criteria that is taken into consideration while opting for relocation or is it completely voluntary? What if you don't have enough bandwidth or floor strength from your current project in these locations .. do they still allow you to relocate ??

like

Guys, I have one offer from Quess Corp and client is Shell India. Offered salary 45lpa fixed.
As it's 3rd party payroll i am thinking to reject it. What are your thoughts.

Stack -- python, kubernetes, flask
Yoe -- 8.5

like

Do EPAM Systems employees have to go through the same process of client interviews to allocated to a project? I heard they don't. So if Anywhere and Systems are same then why this difference? Can anyone please let us know the facts.

likefunny

Hi Fishes need your suggestions. I had an interview with a small company last week and this week they scheduled a client interview. The manager person is trying hard to lock me by saying not to accept any other offers till they confirm and he made me attend a demo call today to teach on how to take client interview. I didn't feel good with this approach like treating school kids.

Should I accept the offer if I clear client interview and use this to counter for others? Or should I reject?

like

Hi All,

I have an offer from GlobalLogic (client Expedia Group).
Below is the salary structure.

The thing is, I have an offer from another company as well, and they are offering around 1.7lpa more.
Can I ask GL to match the offer?
Only 1 week is left in my LWD.
Skills Java springboot microservice
YOE -3

Infosys Tata Consultancy Accenture Deloitte Adidas Cognizant UKG Wipro

Post Photo
like

More Posts

Is there anyone from Deloitte who joined TCS?

likefunny

What’s your favorite must try restaurants that’s open in the city this summer? Visiting for the first time. No dietary restrictions and open to any cuisines (preferably something uniquely chicago besides deep dish and portillos)

like

Hi. Recently have got an offer from CGI. I have heard a lot of good things about this organisation. I am dropping my joining to one of the Big 4 to join here. Is it worth it? Please comment folks.

like

Just picked up my wedding dress and so excited for my wedding. My future sister-in-law is getting married next year in May and she is sending me pictures of what styles she likes. They all kind of look like the dress I chose 😂. She didn’t come shopping with me so she doesn’t know what I picked. Do I even mention thag my dress looks similar when we do go shopping next month? Or maybe she won’t even notice?

like

I am joining the McKinsey & Company Boston office in late July and searching for housing in the meantime. If I could get some insight on the ratio of days in the office vs work from home that would be super helpful!

If I need to come into the office regularly I will try to find a place close by.

like

Does anyone have a Coravin? If so, would you buy it again?

Thinking about picking one up!

like

Capital One 🐠: how’s the digital strategy group? Is head of digital strategy a director level role?

like

Hey fishes, Just want to know does Barclays india hires exl services employee or is there any bond between them in which exl employee can not join?

Please help
barclays?Barclays global service center Barclays EXL Service Exl Pvt Ltd

like

Hi, I was a flight attendant for about 3 years and I’ve decided not to continue flying. I love the aviation industry and was wondering what are some other options in aviation that don’t require additional education? I have a bachelors degree but it’s completely unrelated to aviation

like

I'm a recent qualified ACCA member (3 year exp in corporate finance). Looking for a role in M&A space preferably in main land Europe. Would appreciate any guidance on how I should approach the job market and what kind of roles I should shoot for. TIA

like
like

How important is it to attend admissions events , set up calls, or have on campus visits? How many of you did none of those?

like

How do people save money to get to MBA? Non-tech/consulting/finance background.

like

What is average salary for Test Lead in Netherlands with 9 years of experience

Hi Fishes
I have an interview scheduled with
Accenture India Strategy for the manager position in there Supply Chain & Operations practice.
Any ideas on what kind of questions I should expect?
Case study?
Guesstimate?
Scenario based questions?
Any inputs would be really helpful.

Thanks in advance!
Accenture India

like

Anyone from/around Rajahmundry?

like

Anyone who joined Kyndryl Bangalore recently like may be in last 6 months.Can they share their feedback about work culture,Wlb,job security and if its a safe option to join .I got an offer but i am in a Product based company. So little worried if I should join or not.Main concern is job security atleast for another 3 years.Please help guys to decide.

like

Hello fishes,
Any one from HSBC Hyderabd ..how’s the wlb and job security for ETL developer .

like

Did anyone get approval on RFE for specialty occupation? EY manager here

Ladies, where do you order cosmetics from indian brands like kama ayurveda and forest essentials? Amazon seems unreliable

like

Additional Posts in Excel Genius

So i have a list of companies in a list (column A), and a “Y” next to them (column B) if i want to include them in my dataset.
Is there any way i can make a variable list, ie below the full dataset, of just the companies with “Y”, and it being dynamic?
Can post a screenshot tomorrow if useful to explain

like

Looking to use an XIRR formula with an “As of” date option (as of 12/31, 9/30, etc…) So it would essentially stop pulling dates and values once it hits the date in the “as of” date cell.

Any ideas? Much appreciated.

like

Question, maybe you will be able to help me solve this.
I created a data base in excel for work. I have a new sheet "A" that pulls from sheet "b" when you enter an exit date, 5 cells per person from "B" to "a" using formula =filter(sheeta!a3:e41,sheeta!e3:e41) on sheet "b". This worked perfectly. My not so excel oriented co worker manually copied the information from sheet "a" to sheet "b". At this point all formula cells turned to "spill"error. Help!

like

I am trying to create a formula based on data type geography. I want to ba able to write if(cell=NY,,) but I get #value.

How do I get formula to recognize the text of a geography data type?

like

I have a 30 minute excel study case as the final step for a potential job. It would be a great raise and a step into the field I ultimately want to be in. The hiring manager told me that it is a basic test, more to see how much I know. However, I want to exceed expectations. I do use excel daily, but nothing too complicated. Does anyone have any tips or resources that might help me wow upper management and land this role? Thank you in advance.

likehelpful

How can I track a pivot chart to its origin pivot. Have a ton of charts and pivots, so trying to find an easy way to see which chart links to which pivot. Thanks in advance.

like

Our Data is only showing when something has changed, is there any way to make excel fill in the values?

Post Photo
like

Just created my first macro! Super proud of myself! #humblebrag

likeuplifting

I have a data set of 150k user pins and another data set of 200k pieces of hardware + the user pin assigned it + its model. Each piece of hardware is 1 of 10 possible models. Some users have >1 piece of hardware/hardware model.

I need to display user pins & indicate which model(s) each has. Im using COUNTIFS and have a column for each model and a number at the intersection between pin/model. This is using >1M functions which is causing issues with size/speed.

Any recommendations?

like