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

Joined a new firm and they want us to go get pictures taken to put online. Would it be inappropriate to use a headshot from a prior firm (in a diff country)?

like

This has been asked but looking for fresh perspectives....tips on giving your notice at your shop? Particularly a place you can’t wait to leave...but wanting to leave on an amicable note.

Planning a San Juan trip soon. Went two years ago and stayed at an Airbnb in Old Town, but I feel more inclined to stay at a more modern hotel this time. Any recs? And is it a hassle to get in between Old town and the newer area? I did enjoy being able to walk everywhere in Old town….

like

Agency or in-house hiring senior copywriters in Chicago?

Hi Guys,

Can I use a counter offer to renegotiate with KGS after accepting the offer?
What are the chances that they will increase even if not match the offer and will they retract the current offer if counter offer is presented?

like

Which comoanies in NYC can sponsor H1B visa? I am an Audit professional who works for KPMG and wanted to make a move to NYC.

like

Any Salesforce-related roles paying around $130k-$150k? I am in Madison, WI with 6 years of Salesforce experience. Not exactly sure what next role I should pursue. I’m not asking for current postings, but wondering if there are any types of roles with that pay.

like

Honest question: Is the trans community that fragile that we have to modify all kinds of words and how we speak or are “Allies” just going overboard?

likefunny

How soon to take time off as first year? And how much?

like

Curious about which firms have strong presence in healthcare fraud (Fee for service)

like

Successfully made the jump.

Places where I applied and got an interview so at least I know they look at fed backgrounds. Might want to check them out. These were DC area.

Amazon
Google
Marriott
Visa
Facebook

likehelpful

Anyone know of any dealers in US not seeking market adjustments (currently c. $10K premium) on MB sprinters?

like
like

What are the actual benefits of marriage?

like

So I know the big 4 are hiring like crazy right now. Is that the same case with MBB?

like

Buckle up everyone, the holiday season has officially started. Time for endless carols and overplayed Christmas songs on the speakers all day long 😑

likehelpfulfunny

Jp Morgan HR round scheduled tomorrow, what kind of questions to expect?

Having 7 years of experience in Mainframe current CTC is 20.5lpa fixed, how much Max package i can ask for?

Please help as this is my first switch in whole career

like

Anyone uses services online to help with resume building? Hoping to leave Big 4 soon and move to industry.

like

Anyone invest in Dogecoin?

like

Looking for finance jobs in UK for someone with 3 years of experience and previously worked at Amazon.

like

Additional Posts in Excel Genius

What’s the keyboard short cut to switch between open apps without using the mousing. It’s something like using alt tab or whatever the combination is

I have a table in sheet1. Column examples are ID, Initiative, Owner, etc. I’d like to use that table as a growing list of inputs. I’d like for sheet2 to be used by another team for other types of data inputs. But I need sheet2 to automatically populate the IDs from sheet1 as they change. Thoughts?

like

Need some help. Is there a fax function (Filter) so that if I want my subtotals to show a number but nothing below it to report. One expansion of a row the subtotals show but no numbers below it.

like

What are the most typical models you all build

likehelpful

Help, simple task can’t figure it or. List of user names in one sheet compare to another list on another sheet and highlight duplicates.

like

Not Excel related, but any folks in strategy willing to suggest the best and most useful data platform to learn? I’m thinking along the lines of Tableau, Power BI, etc.

likehelpful

I have a spreadsheet that includes these columns: total AUM of firm X, % of AUM allocated by X to sector Y, % to sector Z, and so on (one column for each sector). Goal is to present in tabular form...

Just wanna say thank you to this group of beautiful people, always willing to help each other reach a solution!

likeuplifting

Hey guys, I have a Google spreadsheet where each cell has multiple links separated by semi-colons.

How would you get the links to show up in different rows WITHIN a cell? (Pictured is what I want to achieve).

Thank you so much🙏

Post Photo
like
like

There has to be a way to restrict access on a worksheet so that only specific cells can be edited.

No matter how many times I’ve tried to automate and simplify generating reports to just inputting data into specific highlighted boxes, someone just have to try to edit a completely different cell then asking why the worksheet is “broken.”

likefunny

What are the most useful macros you’ve seen that could be used across multiple projects?

like

Hi,
I am trying to match in a specific row of a different sheet, although I want to use a calculated row value to specify the array to look in, see the photo for the logic I am trying to do… any ideas on how to get this to work?

Post Photo
like

*** This is a public announcement ***

PLEASE stop hiding rows and columns and start grouping them instead

— yours, the person who is trying to understand your work

likefunnysmart

Hello again fishies...

Column D in tab 1 and tab 2 is a drop down list which has 6 items.

In tab 3 how can I return the Cells in Column a b and c (tab 1 and 2) when the value in cell d(tab 1 and 2) is one of the 6 items selected

like

I have a table of different tickers of stocks and their according prices on different dates and times (hour and minute shown, no seconds). I'm trying to extract a singular price for each ticker based on date and time, but am having trouble doing so as there's multiple prices for each time (i.e. one row has $10 for 2:50pm, another has $11 for 2:50pm, etc) and was wondering if there was a way to pull only one price, let's say the highest, for a time?

like

Looking to set up conditional formatting to indent a cell if another cell has a certain number of characters. Please see comments for details.

Post Photo
like

At my old firms when I would be using excel and modeling using the font schemes (blue, green, black for hardcode, link, and formula) I could use the “control shift ;” to shift through the schemes. My new firm doesn’t do this. Does anyone know why the shortcut would be different and how I can add it back?

like

Does anyone know why negative IRR sometimes gives you a NUM error? A lot of googling and experimenting but cannot pin down what’s going on.

like

I did this manually but would there be a way to pivot the dates (day/month) in the rows years in columns with raw data being (date/month/year) needed a visual of same day different year

Post Photo

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

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

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