+ Reply to Thread
Results 1 to 4 of 4

Sumifs with OR operator text string search in single cell...

  1. #1
    Registered User
    Join Date
    04-15-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    2

    Sumifs with OR operator text string search in single cell...

    Am trying to calculate the total from an multiple parameter sumif but...the process is somewhat complicated by having an OR operator text search and a desire to limit the addition of a new column.

    I have no problem evaluating each element of the dataset to a true/false state which I can then sumproduct/sumif(s) etc but again I'm really trying to avoid adding another column to what is already a very large data set.

    I feel like I might be missing something fairly obvious about a way to integrate an array function into the calculation of the target value. A simplified example is attached.

    TestSheet.xlsx

    Thanks for any help.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs with OR operator text string search in single cell...

    Try this array formula**:

    =SUM(IF(ISNUMBER(SEARCH(H7,C7:C12))+ISNUMBER(SEARCH(I7,C7:C12)),IF(B7:B12=J7,D7:D12)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    04-15-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Sumifs with OR operator text string search in single cell...

    This works, hadn't considered splitting up the text search like this. Thank you for the help.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs with OR operator text string search in single cell...

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. formula to search for text and display all in single cell
    By gorelordz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-28-2014, 12:12 AM
  2. [SOLVED] How to Exclude text in string in a single cell
    By leopoldc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-28-2013, 07:01 AM
  3. [SOLVED] Insert a NAME into a text string in a single cell
    By cptkeith in forum Excel General
    Replies: 1
    Last Post: 12-17-2012, 06:04 PM
  4. Replies: 7
    Last Post: 03-29-2012, 01:25 PM
  5. Search and replace text in cell with single character
    By piputty in forum Excel General
    Replies: 1
    Last Post: 08-05-2011, 10:32 PM

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