Struggling with this:
Suppose I want to shortlist companies that serve the priority industry vertical of a country. So I have the industries served by company (one column for each industry) and (cont)

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

Since you're looking for yes in certain row locations, I feel like you should use index and if but you might be able to use sumproduct() I'd Google both and try it out. For sumproduct() look for sumproduct() to replace sumif

like

I think you need to build it like a database rather than a table.

Country | Industry | industry served? | primary industry? | score

If the above are each columns, then you can run if statements on that last column to get your score. Then run pivots to get the table view you want

like

This is hacky and not ideal (cleanest, but more complex method probably uses Huron 1’s method. It’ll require some trial and error though), but it’ll get the job done. Assuming your list of countries in in cells A2:A101 (including header), industries across the top in B2:Z2, and yes’s in cells B3:Z101. You have your list of priority countries in cells A1000:A1010 (just somewhere out of the way). In cell B1, put in =IF(ISNA(VLOOKUP(B2, $A$1000:$A$1010,1,0)),FALSE,TRUE). Drag this formula to the right all the way to cell Z1. Now you have T/F across the top for if an industry is priority. In cell AA3, put =IF(AND(B3=“Yes”,B1=TRUE), 3, 0). Drag this formula down and right to AY101. This gives a score of 3 if a company is in an industry and that industry is priority. In cell AZ3, put =SUM(AA3:AY3) and drag this down. Now in column AZ, you will have the sum of scores for each company.

like

(Cont) and yes/no for whether that industry is served.
Then I also have the priority industries for the country.
I want to have an output that says if the company serves the priority industries then it’s a score 3.
How do I do this?

Related Posts

Alvarez & Marsal Hi!
Can anyone shed some light on the Disputes and Investigation practice at Alvarez and Marsal. How’s the work culture and the team? Also, how is the pay-band for someone joining at a Manager level.
Any and all information will be helpful. Alvarez & Marsal

like

PWC India or Capgemini?

like

Do people think working remotely will remove some gender bias in the work place? Or impact people’s perceptions of women colleagues?

like

I had a super that would always appear behind me or circling my car late at night. Turns out it was a “me problem” when I reported it

Post Photo
like

So absolutely over these "looking for advice!" Fishbowl posts where people just want to hear their own opinions repeated back and nothing else.

like

I've just 12 hrs to think about this -- Oracle or Freecharge ?? Work profile with Oracle is development of oracle jet framework and with Freecharge merchant app with freecharge Please help yaaro

like

Is there a legal or corporate law team in KPMG or other big 4s where they hire LLBs or Company secretaries? KPMG Deloitte EY PwC KGS

like

Anyone commuting from Midtown Toronto to Mississauga for work and willing to carpool??

Any one got the retention offer from TCS ?

like

Salary for senior 2 in the LA market at mid tier ?

like

I matched with a guy and we initially agreed to meet for the first time this coming Friday. Last Wed, he sent two messages on the app with suggestions but I wasn’t able to text back until Sat because I was traveling. He unmatched me. Am I in the wrong here? How often do you text people in the talking stage? (F in early 30s here)

like

How do you deal with losing close relative/family? I wake up every day praying I never get a phone call like this. I finally did today, I am not crying but I’m confused, sad, broken, don’t know what to say to others. Don’t even think I can focus the rest of today. It’s so painful that we have to deal with this in life. Apologies if this is not the typical fun post, just feeling helpless. I can’t talk to family too, they feel the same.

like

Management consultant evaluating an offer from Google’s Go-to-market team (Strategy & Operations) in India. Two questions:
1. Given it’s an internal consulting role, are the hours equally bad / worse than consulting firms?
2. Is it virtually impossible to move to a product ladder after spending 1-2 yrs in the role?

like

Best neighborhoods for mid twenties couple for a decent price? Would prefer to be near good restaurants and hopefully around other young people. New to SF so not sure how common this is but love buildings with a gym!

like

Any single females want to grab drinks/dinner in the city?
28M for reference!

likefunnysmart

I work remote from the west coast and I love it. I rarely travel for work but next week I’ll be in our NY office. I’m actually excited to wear work clothes again for 1 week 😂

like

Heading to Auckland for work and have 2 days to relax. Any recommendations on things to do/see this time of year and must do restaurants?

How much are you all paying in center based childcare ? Kiddos ages?

like

Going to the Atlanta Braves game tomorrow. I see there's a ton of shopping centers right next to the park, can I just park there and walk over to the stadium? Wondering what the natives do

like

Please post any creative opportunities in the San Francisco Bay area here. Thank you!

Additional Posts in Excel Genius

Best way to lookup a value with multiple criteria across rows and Columns, for 3 criteria I usually do sum product. Any other tips?

like

Any book recommendations on excel modeling for real world business applications?

like

Does anyone have any tips/tricks for knowing where to plug +1/-1 in a large table with totals across the y and x axis that contain un-rounded numbers? Every time I fix a row I throw off a column or my check figures to the true data source.

Post Photo
like

Does anyone from PwC have a list of the favorite groups shortcuts for excel? Please provide. Much appreciated .

like

How do you fix microsoft excel cannot paste data error?

like

Is there a way for me to automate creating columns to do this so I can drag across and create my columns? For example--
1Q2021 2Q2021 3Q2021 4Q2021 2021 1Q2022?

likesmart

How long did it take you to become good at excel? Like fast?

like

I created a macro to help me save time on this onetime task and now whenever I open my excel, a blank excel opens with the name of the macro. How do I get rid of it?

like

Best websites to get more of a handle on Excel as a beginner-intermediate?

like

I have some data extracted from SAP and though the size is not relatively high lets say about 45000 line items. My excel keeps crashing anything i do with it 🙄! Any clue why it is happening ?

like

I have 1 sheet with names and sheet 2 with a large list of names that may or may not be those names - which also have corresponding unique identity numbers. How do I easily match the names with the same names and keep the unique identity numbers for importing later?

Hi Group, seems like it should be basic format, but I just can not get it to work. a list of data and want to alternate shade (blue,white,blue,etc) each line but if I have multiple lines I want to group together and they all shade the same. I want to group by the same data in column A, and the number of rows that are the same could vary. So let’s say row 1& 2 same entry in column A(blue) row 3 different (white) row 4,5,6 same as each other different to other rows (blue) etc… help 🙏

like

What’s the keyboard shortcut to get into the “insert options” dialogue when you’ve inserted a new row with Ctrl+Shift+Plus?

like

EY trying to move away from Excel . Think it’s possible? Want to see it happen?

like

I’m calculating a bunch of totals and averages by category on one worksheet by filtering data in different categories from another worksheet. However, when I change the filters, the previously..(cont)

How can I copy a formula from one sheet to another?i tried the simple Ctrl+C, Ctrl-V, but the formula refers to the old sheet.

like

Client sent over a 90 MB model this evening. Took 10 minutes to open and a fraction of that to crash. Can’t wait for tomorrow 😭

like

Is there any easy way to handle FedEx/ups tracking numbers? I’d like to have some formula that would just show the status in another cell.

like

Best free data sites to tap into to build a city analysis model? Basically I want to "discover" cities with certain dimensions, like: population, elevation, climate, and proximity to other things (ski resorts, particularly)?

like

What are your top excel functions you use in finance / accounting - besides vlookup - Pivots - sumifs - count - index. I have an interview coming up which will have excel based technical questions. Not sure what other functions are considered important in the accounting finance world.

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal