Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Need to deliver a count of certain names by looking for a string within a string and then counting the names associated with the smaller string

I have a file I have been working on. The formula in B3:E3 was successful in looking up 1 of the 4 objects in A3 and then returning a value associated with the object in a different tab.

Here, I have a new type of problem where I need a formula in B3 on my Formulas tab to look at column A on my Areas tab and count all of the cells that have "LA" or "Las Vegas" for the object names in A3 on the Formulas tab.  The answer should be 2.

Thank you!
Test-File.xlsx
0
Dominator1025
Asked:
Dominator1025
1 Solution
 
barry houdiniCommented:
Hello Dom,

This formula should give you the count for "LA" or "Las Vegas" [edited]

=SUMPRODUCT(ISNUMBER(SEARCH(","&Areas!$A$2:$A$2000&",",","&$A2&","))*(Areas!$B$2:$B$2000={"LA","Las Vegas"}))

That gets the results you suggested but only matches the whole 5 character object

You can change the {"LA","Las Vegas"} part to include any number of locations, if it's a single location you don't need the brackets { and }

regards, barry
0
 
Dominator1025Author Commented:
Awesome, thanks again!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now