+ Reply to Thread
Results 1 to 5 of 5

Sumifs with wild characters and cell references

  1. #1
    Registered User
    Join Date
    09-05-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    27

    Sumifs with wild characters and cell references

    Hello,

    I have the following sumifs formula that I would like to get first three characters out of a cell reference a1 that contains the word Bolivia. I have a list of several countries that i need the result but have several other text right after the name of the country. This way works great: Instead of typing it for every country like this:

    SUMIFS(PEBk,PSL_5,"Bol**")

    Instead of typing it for every country like this, which works great, I would like to use column A that has a list of countries. How can I use the cell reference to express this part "Bol***"

    Thank you very much.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sumifs with wild characters and cell references

    You only need one * - with Bol in A1 try

    =SUMIFS(PEBk,PSL_5,A1&"*")

    or if you just want the first 3 chars from "Bolivia" in A1

    =SUMIFS(PEBk,PSL_5,LEFT(A1,3)&"*")
    Audere est facere

  3. #3
    Registered User
    Join Date
    09-05-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Sumifs with wild characters and cell references

    Thank for replying.

    It is not working.

    Sheet 2 has a sample of the data. I have to use sumifs to consolidate all countries.If I use "Bol*" including the quotes then it works as you can see but I would like to use column A to capture the criteria and convert it "Bol*" as well as for "Bra*" out of the fist three letters of A column.

    Thank you very much for help.
    Attached Files Attached Files

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Sumifs with wild characters and cell references

    The given formula to you by DDL is surely working, in your sample change A1 to A2

    =SUMIFS(PEBk,PSL_5,LEFT(A2,3)&"*")
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Registered User
    Join Date
    09-05-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Sumifs with wild characters and cell references

    Thank you both.

    Sorry Daddylonglegs, I had a typo your solution did work too.

    You have made my day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1