Related Posts
PWC India or Capgemini?
Any one got the retention offer from TCS ?
Additional Posts in Excel Genius
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
PWC India or Capgemini?
Any one got the retention offer from TCS ?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Download the Fishbowl app to unlock all discussions on Fishbowl.
Copy and paste embed code on your site

Scan your QR code to download
Fishbowl app on your mobile

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
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
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.
(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?