+ Reply to Thread
Results 1 to 4 of 4

Sum all the values in a range based on criteria for a range

  1. 03-17-2016, 02:43 AM #1
    Ravana
    Ravana is offline
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2021
    Posts
    346

    Sum all the values in a range based on criteria for a range

    Hi all!!!


    Firstly I confess, I tried all SUMIF and SUMPRODUCT variations but to no avail. So I am seeking help here.

    I want to add all the values in Column B where in first two letters in Column A are "SN".

    SS141500C 3901
    SS073681C 8281
    SS148081C 10330
    SN118132C 2907
    SN118131C 6589
    SN118136C 4753


    Please see the attached work book.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-17-2016, 03:02 AM #2
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,367

    Re: Sum all the values in a range based on criteria for a range

    maybe

    =SUMPRODUCT(--(ISNUMBER(SEARCH("SN",A1:A6))*(B1:B6)))
    Reply With QuoteRegister To Reply
  3. 03-17-2016, 03:03 AM #3
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Sum all the values in a range based on criteria for a range

    =sumif(A1:A6,"SN"&"*",B1:B6)
    Reply With QuoteRegister To Reply
  4. 03-17-2016, 04:53 AM #4
    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: Sum all the values in a range based on criteria for a range

    One way...

    Data Range
    A
    B
    C
    D
    E
    1
    SS141500C
    3901
    SN
    14249
    2
    SS073681C
    8281
    3
    SS148081C
    10330
    4
    SN118132C
    2907
    5
    SN118131C
    6589
    6
    SN118136C
    4753
    7
    ------
    ------
    ------
    ------
    ------


    This formula entered in E1:

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

    Let's Go Pens. We Want The Cup.
    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. Formula to return values based on criteria and date range
    By jr217 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2014, 06:20 AM
  2. [SOLVED] Would like to count cell values based on date criteria in a different range
    By sdavison in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-23-2013, 12:45 AM
  3. Automatically copy values based on unique/new criteria from a range
    By djarcadian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-31-2013, 08:45 PM
  4. [SOLVED] Grouping Values based on a criteria in a Column and MUltiple Rows over a range
    By tahoka in forum Excel General
    Replies: 6
    Last Post: 10-18-2012, 03:10 PM
  5. find values in a range based on criteria
    By Sreejith.ag in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-31-2012, 08:12 AM
  6. Finding values in a range based on criteria and listing them consecutively?
    By Alabaster in forum Excel General
    Replies: 5
    Last Post: 10-29-2010, 09:35 PM
  7. sum the values of a range based upon multiple ranges and criteria
    By LiveIt... in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2006, 04:23 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1