+ Reply to Thread
Results 1 to 7 of 7

Return sum of adjacent matching cells

  1. #1
    Registered User
    Join Date
    10-14-2010
    Location
    BC Canada
    MS-Off Ver
    Excel 365
    Posts
    78

    Cool Return sum of adjacent matching cells

    Not sure if my title makes sense. I want a function that will take a key word(s), and sum the values next to them.
    For example if I entered Car into the function I would get 5 returned, truck, 7, bike, 9.


    Car 1
    Truck 2
    Bike 3
    Car 4
    Truck 5
    Bike 6

    I hope that is clear.

    Thanks

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

    Re: Return sum of adjacent matching cells

    Like this...

    =SUMIF(A1:A6,"Car",B1:B6)

    Or, using a cell to hold the criteria...

    D1 = Car

    =SUMIF(A1:A6,D1,B1:B6)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,306

    Re: Return sum of adjacent matching cells

    Sorry thisandthat,

    Not clear! Define "next to them". Is that after and before? How did you get from "Car" to 5 given the table above?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    10-14-2010
    Location
    BC Canada
    MS-Off Ver
    Excel 365
    Posts
    78

    Re: Return sum of adjacent matching cells

    Thanks Tony, way to easy

    4+1 = 5

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

    Re: Return sum of adjacent matching cells

    You're welcome. Thanks for the feedback!

  6. #6
    Registered User
    Join Date
    10-14-2010
    Location
    BC Canada
    MS-Off Ver
    Excel 365
    Posts
    78

    Re: Return sum of adjacent matching cells

    Should the sumif function add all cells in the range? I just get the first cell.

    For example
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Returns 5 (Cell B1 + B4), not 22 (Cell, B1 + C1 + B4 + C4)
    I can do a work around, but it's not very elegant.

    Thanks again

    A B C
    1 Car 1 7
    2 Truck 2 8
    3 Bike 3 9
    4 Car 4 10
    5 Truck 5 11
    6 Bike 6 12

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

    Re: Return sum of adjacent matching cells

    Quote Originally Posted by thisandthat View Post
    =SUMIF(A:A,"A",B:C)
    The sum range B:C has to be a single column.

    You could do like this...

    =SUMIF(A:A,"A",B:B)+SUMIF(A:A,"A",C:C)

+ 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. [SOLVED] Return 1 if sum of adjacent matching cells equals zero
    By marcojhb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-23-2016, 09:33 AM
  2. Replies: 2
    Last Post: 09-07-2013, 07:50 PM
  3. [SOLVED] Matching cell to range, and copying adjacent cells.
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-14-2012, 12:11 PM
  4. [SOLVED] Matching last 5 digits of a number to a range and copying adjacent cells.
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-20-2012, 11:17 AM
  5. Replies: 2
    Last Post: 06-12-2012, 06:39 PM
  6. matching string from a list and copying adjacent cells
    By Derivatives in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2011, 11:07 AM
  7. Excel 2007 : Formulae to count matching adjacent cells?
    By Madraykin in forum Excel General
    Replies: 10
    Last Post: 03-03-2010, 09:18 AM

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