+ Reply to Thread
Results 1 to 4 of 4

Sum of Cells in One Range Dependent on Specific Text in a Different Range

  1. #1
    Registered User
    Join Date
    01-11-2012
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2006
    Posts
    29

    Sum of Cells in One Range Dependent on Specific Text in a Different Range

    I am using Excel 2016. I need to create a formula that will provide me with a total of the numbers in the cells in one range that meet the criteria of specific text in a different range. Range X contains the specific text and Range Y contains the numbers that need to be added together if they match the text in Range X. I entered the following formula and received a message of "You've entered to few arguments for this function."

    Formula Entered: =SUMIFS(G:G,"Text",E:E,">=1")

    I previously asked this question with the following information and examples:

    Range X contains the specific text and Range Y contains the numbers that need to be added together if they match the text in Range X.
    I tried using SUMPRODUCT as follows but was not successful:
    =SUMPRODUCT((RANGE X,"TEXT")*(RANGE Y,"=>1"))
    =SUMPRODUCT((RANGE X,"TEXT")*(RANGE Y, =>1))

    The feedback I received was as follows:

    1. =SUMPRODUCT((B1:B10="TEXT")*(C1:C10>=1))
    but you could use countifs
    =COUNTIFS(B1:B10,"text",C1:C10,">=1")
    mind you this is sum so
    =SUMPRODUCT((B1:B10="TEXT")*(C1:C10>=1),c1:c10)
    or
    =SUMIFS(C1:C10,B1:B10,"text",C1:C10,">=1")

    Note: I used the last option and I thought it worked but I can't find my source.

    I'd appreciate any thoughts, etc.

    JazziML

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.86 for Mac MS 365
    Posts
    8,525

    Re: Sum of Cells in One Range Dependent on Specific Text in a Different Range

    when using sumifs the first range in the formula, G:G in your example is the range you are summing - should have the numbers, then the next range would be where you find "Text" so that needs to be the column that has "text" in it. Then the next range it appears you are saying that you want to look at column E and ">=1" should be ">="&1.
    hope that helps.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    01-11-2012
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2006
    Posts
    29

    Re: Sum of Cells in One Range Dependent on Specific Text in a Different Range

    Sambo kid,

    I followed your input and the resulting formula worked for me. I have to be honest I don't understand the ending but it works and that is what is most important.
    I did the "Add Reputation" part.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.86 for Mac MS 365
    Posts
    8,525

    Re: Sum of Cells in One Range Dependent on Specific Text in a Different Range

    Good, glad that worked for you, AND thank you for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 10
    Last Post: 05-28-2018, 09:29 PM
  2. Search for a non-specific text in range of cells
    By Laatte in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2016, 04:17 PM
  3. [SOLVED] Return specific text if a different text is contained anywhere in a range of cells
    By ciayers in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2013, 05:54 PM
  4. Replies: 7
    Last Post: 07-30-2012, 01:31 PM
  5. Finding the range of cells that contain specific text
    By WerdnaSbbig in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2012, 10:33 AM
  6. Replies: 2
    Last Post: 07-12-2009, 05:14 PM
  7. How to filter a range of cells for specific text?
    By talk2mide in forum Excel General
    Replies: 2
    Last Post: 03-01-2007, 05:35 PM

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