SUMIFS Across Rows with Multiple Criteria
Results 1 to 3 of 3

Thread: SUMIFS Across Rows with Multiple Criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIFS Across Rows with Multiple Criteria

    Hi Group!

    I've got a question on using SUMIFS to pull data horizontally with multiple criteria. I have a table of data in which the column headers are month end dates, and each row corresponds to a unique account with monthly data amounts.

    Example

    A B C D E
    1 1/31 2/28 3/31 4/30
    2 103 2 3 1 4
    3 101 3 2 1 1
    4 102 4 2 0 1
    5 104 0 0 2 0


    I want to write a SUMIFS statement which will sum columns B to E where column A equals a certain account, and where row 1 (with the month end dates) is less than or equal to the current month end on another sheet. So, if the month end date were 3/31 and we wanted the year to date total for account 101, the result would be 6.

    I have tried to SUMIFS(B:E, B1:E1, <= 3/31, A1:A5, "101") I'm getting a value error which I cant seem to get around.

    Any help would be greatly appreciated.

    Thank you.

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: SUMIFS Across Rows with Multiple Criteria

    Welcome to Mr Excel Forum

    Dates as mm/dd/yyyy

    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    01/31/2019
    02/28/2019
    03/31/2019
    04/30/2019
    Account
    Ref Date
    Result
    2
    103
    2
    3
    1
    4
    101
    03/31/2019
    6
    3
    101
    3
    2
    1
    1
    4
    102
    4
    2
    0
    1
    5
    104
    0
    0
    2
    0
    6


    Criteria in G2:H2

    Formula in I2
    =SUMIF(B$1:E$1,"<="&H2,INDEX(B$2:E$5,MATCH(G2,A$2:A$5,0),0))

    Hope this helps

    M.

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS Across Rows with Multiple Criteria

    Thanks Marcelo for the prompt response! That worked great. Thank you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •