+ Reply to Thread
Results 1 to 13 of 13

SUMIF with single criteria for multiple adjacent rows

  1. #1
    Registered User
    Join Date
    08-06-2021
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    6

    SUMIF with single criteria for multiple adjacent rows

    Dear All

    I have a table as attached. Could anybody help me to sum in column B with the value in Column A is "James". For one value of "James" in column A, I have three adjacent related value in B. How to "SUMIF" with range A1:A24, criteria "James", sumrange B1:B24. One "James" in column A relates to three row values in column B. For example, A1 relates to values of B1, B2, B3. And so on.

    Thank you very much/

    Image.png
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: SUMIF with single criteria for multiple adjacent rows

    Hi,

    And welcome to the forum

    It's not clear exactly what you want. In any case this is probably clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

    That said with anything like this you need every cell in column A to have a value. The easiest way of achieving this is:

    1. Select all of the column A cells where there are values in column B
    2. Click the F5 key, choose 'Special' and select 'blanks' and OK
    3. enter the = sign and then immediately the up arrow
    4. Select the CTRL key and with that held down click the Enter Key.

    Now the whole of column A has values.

    Add a couple of field headers in a new row 1 and use a Pivot Table to summarise your data.

    See attached
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: SUMIF with single criteria for multiple adjacent rows

    Can't you fill in the names in the blank cells in column A?

    Pete

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: SUMIF with single criteria for multiple adjacent rows

    You could fill the names down column C using this formula in C1:

    =LOOKUP("zzz",A$1:A1)

    then you could use this formula to get your total:

    =SUMIF(C:C,"James",B:B)

    Hope this helps.

    Pete

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: SUMIF with single criteria for multiple adjacent rows

    Cell D2 array formula , Drag down
    HTML Code: 
    Cell E2 array formula , Drag down
    HTML Code: 

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: SUMIF with single criteria for multiple adjacent rows

    Another way:

    =SUM(SUMIF($A$1:$A$24,"James",OFFSET($B$1:$B$24,{0,1,2},0)))

  7. #7
    Registered User
    Join Date
    08-06-2021
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIF with single criteria for multiple adjacent rows

    Thank you Richard, I may not stated cleary what I would like to do. I would not be allowed to fill down column C with "James" as you guided. I am trying to figure out

  8. #8
    Registered User
    Join Date
    08-06-2021
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIF with single criteria for multiple adjacent rows

    Quote Originally Posted by Pete_UK View Post
    You could fill the names down column C using this formula in C1:

    =LOOKUP("zzz",A$1:A1)

    then you could use this formula to get your total:

    =SUMIF(C:C,"James",B:B)

    Hope this helps.

    Pete
    Thank Pete this would be the simplest and quickest way but I am not allowed to do it to keep the data easier to research. Let me think about it. Thank you very much

  9. #9
    Registered User
    Join Date
    08-06-2021
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIF with single criteria for multiple adjacent rows

    Quote Originally Posted by wk9128 View Post
    Cell D2 array formula , Drag down
    HTML Code: 
    Cell E2 array formula , Drag down
    HTML Code: 
    Thanks Pete it works. I have to learn much more..

  10. #10
    Registered User
    Join Date
    08-06-2021
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIF with single criteria for multiple adjacent rows

    Quote Originally Posted by Phuocam View Post
    Another way:

    =SUM(SUMIF($A$1:$A$24,"James",OFFSET($B$1:$B$24,{0,1,2},0)))
    Dear Phuocam , thank you very much I sucessfully got it. I need much more learning as I had no idea with array formula. By the way could I ask you for more as follows: if the data in column B (which corresponding to "James", for example, in this case are three numbers) increase to 4 numbers or 5 numbers, or decrases to 2 numbers, then which element in array formula {0,1,2}.. must be adjusted accordingly.

    Thank you very much/

  11. #11
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: SUMIF with single criteria for multiple adjacent rows

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'.

  12. #12
    Registered User
    Join Date
    08-06-2021
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    6

    Re: SUMIF with single criteria for multiple adjacent rows

    Quote Originally Posted by wk9128 View Post
    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'.
    Dear wk9128, thank you for comment. How to mark this thread as "Solved". Sorry I am totally new in this forum, and I have been looking around but didn't see where to mark. Thank you

  13. #13
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: SUMIF with single criteria for multiple adjacent rows

    Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post.

    If you think who helped you, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to your reputation there
    Attached Images Attached Images
    Last edited by wk9128; 08-07-2021 at 11:59 PM.

+ 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. Sum Count of Single Criteria in Multiple Non-Adjacent columns
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  2. Sum Count of Single Criteria in Multiple Non-Adjacent columns
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  3. [SOLVED] Sum Count of Single Criteria in Multiple Non-Adjacent columns
    By Domenic in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 09-06-2005, 04:05 AM
  4. [SOLVED] Sum Count of Single Criteria in Multiple Non-Adjacent columns
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. [SOLVED] Sum Count of Single Criteria in Multiple Non-Adjacent columns
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. [SOLVED] Sum Count of Single Criteria in Multiple Non-Adjacent columns
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Sum Count of Single Criteria in Multiple Non-Adjacent columns
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. [SOLVED] Sum Count of Single Criteria in Multiple Non-Adjacent columns
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-14-2005, 05:06 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