+ Reply to Thread
Results 1 to 4 of 4

sumif/sumifs to sum one row below criteria row

  1. #1
    Registered User
    Join Date
    02-23-2021
    Location
    chicago
    MS-Off Ver
    excel for mac v16.45 (microsoft 365)
    Posts
    2

    sumif/sumifs to sum one row below criteria row

    row A B
    1 abc -1
    2
    3 def -1
    4
    5 abc
    6 2
    7 abc
    8 -3
    9 abc
    10 1
    11 def
    12 4
    13 abc -3

    i have data in two different columns: column A has a string of letters only in odd rows and column B an associated number which is either in the same row or one below. i'm interested in finding the sum of associated numbers from column B given that the string in column A is "abc" - i.e. if A1="abc" i want to return the sum of B1 and B2.

    i tried using =SUMIFS(B2:B13,A2:A13,"abc") but that only returns the sum of odd rows. how do i tell excel to look in cell A1 for "abc" and if it it returns B1+B2?

    thanks in advance!
    Last edited by sadgtq84wtohasd; 02-23-2021 at 05:06 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: sumif/sumifs to sum one row below criteria row

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

    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-23-2021
    Location
    chicago
    MS-Off Ver
    excel for mac v16.45 (microsoft 365)
    Posts
    2

    Re: sumif/sumifs to sum one row below criteria row

    yes it did, many thanks

    do you have any resources you'd recommend for learning how to use excel at an expert level? or is it more learned through trial and error?

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: sumif/sumifs to sum one row below criteria row

    For me, it's trial and error. I would say most of what I've learned, I've learned in this forum. I'd suggest browsing through the threads each day and, when you see something that either interests you or you think would be something you would use, watch that thread.

    Glad I could help.

+ 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] SUMIF with multiple criteria (maybe SUMIFS better)
    By foshizzle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2019, 10:59 AM
  2. formula to sumif/sumifs two criteria
    By gaudiumpenrith in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-17-2015, 12:53 PM
  3. Sumif\Sumifs - Multiple criteria - Need help
    By maddy_82 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-27-2015, 12:14 PM
  4. Sumif\Sumifs - Multiple criteria - Need help
    By maddy_82 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-30-2015, 08:28 AM
  5. [SOLVED] Sumif/Sumifs multiple criteria OR instead of AND
    By CRIMEDOG in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2014, 11:30 AM
  6. [SOLVED] SUMIF or SUMIFS with Array Criteria
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2014, 11:25 AM
  7. How to SUM using some odd criteria? SUMIFS or SUMIF will not work...
    By joaolopes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2013, 01:12 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