+ Reply to Thread
Results 1 to 6 of 6

Using SEARCH for Columns

Hybrid View

  1. 07-12-2015, 06:07 PM #1
    Shippior
    Shippior is offline
    Registered User
    Join Date
    07-12-2015
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    2

    Using SEARCH for Columns

    Hey all,

    So I am currently making a spreadsheet to see which costs apply to what person.

    Basically what I want is to have 3 columns, lets call them A, B & C. In A I have a list of names, in B I have a list of numbers while C is either empty or marked with an 'x'. Then I want to determine the name in A and assign the value of B in a new column, D, but only when C is empty. Currently I am using to determine this for a single cell:

    =IF(ISNUMBER(SEARCH("name",A2)),IF(ISBLANK(C2),B2,"0"), "0")

    However, I want to be able to SUM all the numbers that are returned from B. Whenever I refer to columns I only get the first value in the column with respect to that name. For example, name Z is present in A2,A5 & A6. I then want to have in D1 the sum of B2,B5 & B6. Is there an easy way to get the sum of all the values of B for which A is valid?
    Reply With QuoteRegister To Reply
  2. 07-12-2015, 06:55 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using SEARCH for Columns

    First thing...

    In formulas, don't quote numbers. That turns them into TEXT values.

    =IF(ISNUMBER(SEARCH("name",A2)),IF(ISBLANK(C2),B2,0),0)

    After that, sounds like you want to do a SUMIF formula.

    =SUMIF(A2:A10,"name",B2:B10)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 07-12-2015, 07:02 PM #3
    Mazzaropi
    Mazzaropi is offline
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Re: Using SEARCH for Columns

    Shippior, Good evening.

    Try to use:
    =SUMPRODUCT((A1:A10=A2)*(C1:C10<>"x")*(B1:B10))

    Please, tell us if it worked for you.

    I hope it helps.
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marc?lio Lob?o
    Reply With QuoteRegister To Reply
  4. 07-12-2015, 07:06 PM #4
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Using SEARCH for Columns

    I'm thinking...
    =SUMIFS(B:B,C:C,"",A:A,A1)
    in D1, copied down.

    However, you will get the same "sum" result for every row that has the same name in column A...

    If you want to put the name in each formula as you go down column D, replace A1 in formula with "name". The other option is to change the reference to E1, and put the name in column E, of course.
    Last edited by jhren; 07-12-2015 at 07:10 PM.
    Reply With QuoteRegister To Reply
  5. 07-13-2015, 01:34 PM #5
    Shippior
    Shippior is offline
    Registered User
    Join Date
    07-12-2015
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    2

    Re: Using SEARCH for Columns

    Thanks for the help all. In the end I decided to go with the solution Mazzaropi gave as I thought it was the most clear. This method works, I have not tested the other ones.
    Reply With QuoteRegister To Reply
  6. 07-13-2015, 01:50 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using SEARCH for Columns

    Quote Originally Posted by Shippior View Post
    In the end I decided to go with the solution Mazzaropi gave as I thought it was the most clear.
    If you're using Excel 2010 as your profile indicates then you can use the more efficient SUMIFS function:

    =SUMIFS(B1:B10,A1:A10,A2,C1:C10,"<>")

    You may have to replace the commas with semi-colons depending on your regional settings.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help: Adding additional search features ComboBox/DropDownList to search specific columns
    By khhoa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-02-2014, 05:17 PM
  2. Search multiple columns for with multiple input for search and return all results
    By sdl2 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-22-2014, 08:47 AM
  3. [SOLVED] .Find Help - Search only in specific columns (or ignore other columns)?
    By NewYears1978 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2014, 11:32 AM
  4. Search columns of worksheets, if the columns contain data, then form a list on another wor
    By mctsd1981 in forum Excel General
    Replies: 3
    Last Post: 04-17-2013, 07:09 PM
  5. search in different columns
    By Dien in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-30-2008, 05:00 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1