Sumifs Multiple
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Sumifs Multiple
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2014
    Posts
    733
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Sumifs Multiple

    Good evening all

    Trying to sum/combine Column D values.

    1. Monday & Tuesday
    2.Thursday & Wednesday
    3. Friday & Saturday

    If Column E = TRUE and Column C = 2 for each unique ID.

    attempted formula = =+IF(AND($E2=TRUE,$C2=2),IF(OR($B2="Monday",$B2="Tuesday"),SUMIFS(D$2:D$57,$B$2:$B$57,"Monday",$A$2:$A$57,$A2)+SUMIFS(D$2:D$57,$B$2:$B$57,"Tuesday",$A$2:$A$57,$A2),0))

    ID Day Code Value TRUE FALSE
    131 Thursday 2 22.4 TRUE
    131 Friday 2 22.0 TRUE
    131 Saturday 2 24.8 TRUE
    131 Wednesday 2 25.6 FALSE
    131 Monday 2 22.9 FALSE
    131 Tuesday 2 23.1 FALSE
    131 Sunday 2 0.0 FALSE
    132 Thursday 2 23.5 TRUE
    132 Friday 2 28.3 TRUE
    132 Saturday 2 30.9 TRUE
    132 Tuesday 2 29.4 FALSE
    132 Wednesday 2 30.2 FALSE
    132 Monday 2 27.9 FALSE
    132 Sunday 2 0.0 FALSE
    133 Thursday 2 36.8 TRUE
    133 Friday 2 34.3 TRUE
    133 Saturday 2 33.1 TRUE
    133 Tuesday 2 37.9 FALSE
    133 Monday 2 41.2 FALSE
    133 Wednesday 2 31.5 FALSE
    133 Sunday 2 0.0 FALSE
    134 Thursday 2 34.9 TRUE
    134 Friday 2 25.7 TRUE
    134 Saturday 2 20.7 TRUE

  2. #2
    Board Regular
    Join Date
    Dec 2017
    Posts
    86
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs Multiple

    Hi,

    Try this formula

    =SUMIFS(D2:D25,B2:B25,"Monday",C2:C25,2,E2:E25,"True")+SUMIFS(D2:D25,B2:B25,"Tuesday",C2:C25,2,E2:E25,"True")

  3. #3
    Board Regular
    Join Date
    Oct 2008
    Posts
    995
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs Multiple

    Try this

    =SUM(SUMIFS(D2:D25,B2:B25,{"Monday","Tuesday"},C2:C25,2,E2:E25,"True"))

  4. #4
    Board Regular
    Join Date
    Aug 2015
    Posts
    766
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sumifs Multiple

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    ID
    Day
    Code
    Value
    TRUE FALSE
    2
    131
    Thursday
    2
    22.4
    TRUE
    Monday Thursday Friday
    3
    131
    Friday
    2
    22
    TRUE
    Tuesday Wednesday Saturday
    4
    131
    Saturday
    2
    24.8
    TRUE
    131
    0
    22.4
    46.8
    5
    131
    Wednesday
    2
    25.6
    FALSE
    132
    23.5
    0
    59.2
    6
    131
    Monday
    2
    22.9
    FALSE
    133
    0
    36.8
    67.4
    7
    131
    Tuesday
    2
    23.1
    FALSE
    134
    0
    34.9
    46.4
    8
    131
    Sunday
    2
    0
    FALSE
    9
    132
    Tuesday
    2
    23.5
    TRUE
    10
    132
    Friday
    2
    28.3
    TRUE
    11
    132
    Saturday
    2
    30.9
    TRUE
    12
    132
    Tuesday
    2
    29.4
    FALSE
    13
    132
    Wednesday
    2
    30.2
    FALSE
    14
    132
    Monday
    2
    27.9
    FALSE
    15
    132
    Sunday
    2
    0
    FALSE
    16
    133
    Thursday
    2
    36.8
    TRUE
    17
    133
    Friday
    2
    34.3
    TRUE
    18
    133
    Saturday
    2
    33.1
    TRUE
    19
    133
    Tuesday
    2
    37.9
    FALSE
    20
    133
    Monday
    2
    41.2
    FALSE
    21
    133
    Wednesday
    2
    31.5
    FALSE
    22
    133
    Sunday
    2
    0
    FALSE
    23
    134
    Thursday
    2
    34.9
    TRUE
    24
    134
    Friday
    2
    25.7
    TRUE
    25
    134
    Saturday
    2
    20.7
    TRUE


    I4=SUMPRODUCT(SUMIFS($D$2:$D$25,$A$2:$A$25,$H4,$B$2:$B$25,I$2:I$3,$E$2:$E$25,$E$2,$C$2:$C$25,$C$2)) copy down and across


  5. #5
    Board Regular
    Join Date
    Mar 2014
    Posts
    733
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs Multiple

    A better explanation.


    if Column E is TRUE, Column C is equal to 2. sum both values of the same ID TRUE AND FALSE.

    In the table above I need to sum the value of ID 132 and place the result adjacent to the cell where there is a TRUE value in column E.

    below is a smaller subset of the explanation. The result would be 52.86 the sum of values for ID 132.

    The formula I am using above does work, however I would have to separate the Monday + Tuesday Wednesday + Thursday & Friday + Saturday data and then combine. Looking to perform with one function.


    132 Monday 2 23.5 TRUE
    132 Friday 2 28.3 TRUE
    132 Saturday 2 30.9 TRUE
    132 Tuesday 2 29.4 FALSE
    132 Wednesday 2 30.2 FALSE

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Sumifs Multiple

    What is or are expected values given the sample of post #1 ?
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular
    Join Date
    Mar 2014
    Posts
    733
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs Multiple

    Original Post may not provide the most accurate explanation. The subsequent post (Post 6) provides a better example of what I am attempting to accomplish.

  8. #8
    Board Regular
    Join Date
    Oct 2008
    Posts
    995
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs Multiple

    What value do you need adjacent to FALSE?

    the below formula will result as "No Value needed" against all the False.

    =IF(E2=False,"No Value needed",SUM(SUMIFS(D$2:D$25,B$2:B$25,{"Monday","Tuesday"},C$2:C$25,2)))

  9. #9
    Board Regular
    Join Date
    Mar 2014
    Posts
    733
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs Multiple

    a value of zero. Post 8 only provides sums for Monday & Tuesday

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Sumifs Multiple

    Quote Originally Posted by billandrew View Post
    Original Post may not provide the most accurate explanation. The subsequent post (Post 6) provides a better example of what I am attempting to accomplish.
    The same question for the data of post #6 ...
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •