Results 1 to 8 of 8

Thread: Sumifs with or?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2007
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sumifs with or?

    I have a spend value in column C that is associated with a Costs Centre in column A and a nominal code in column B.

    I then have a series of costs centres in cells E1:E6 and list of nominal codes in F1:F6.

    I want the sum of spend for all combinations of Cost Centre and Nominal Code entered in E1:F6.

    I know I can do this with multiple Sumifs, but is there a neater way to do this?

    i.e. =Sum(Sumifs(C:C,A:A,E1,B:B,F1),Sumifs(C:C,A:A,E1,B:B,F2),Sumifs(C:C,A:A,E1,B:B,F3),Sumifs(C:C,A:A,E1,B:B,F4),Sumifs(C:C, A:A,E1,B:B,F5),Sumifs(C:C,A:A,E1,B:B,F6),Sumifs(C:C,A:A,E2,B:B,F1)... Sumifs(C:C,A:A,E6,B:B,F6)

    Any help gratefully appreciated.

  2. #2
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs with or?

    Hi.

    =SUMPRODUCT(SUMIFS(C:C,A:A,E1,B:B,F1:F6))

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    New Member
    Join Date
    Apr 2007
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs with or?

    Thanks for such a quick response, but the formula doesn't seem to work. I think I've not explained what I am looking for sufficiently clearly.

    The formula you've given calculates the sum of the sumifs as follows:

    A:A = E1 AND B:B = F1
    A:A = E2 AND B:B = F2
    A:A = E3 AND B:B = F3
    A:A = E4 AND B:B = F4
    A:A = E5 AND B:B = F5
    A:A = E6 AND B:B = F6

    I am looking to calculate as follows:

    A:A = E1 AND B:B = F1
    A:A = E1 AND B:B = F2
    A:A = E1 AND B:B = F3
    A:A = E1 AND B:B = F4
    A:A = E1 AND B:B = F5
    A:A = E1 AND B:B = F6

    A:A = E2 AND B:B = F1
    A:A = E2 AND B:B = F2
    A:A = E2 AND B:B = F3
    A:A = E2 AND B:B = F4
    A:A = E2 AND B:B = F5
    A:A = E2 AND B:B = F6

    A:A = E3 AND B:B = F1
    A:A = E3 AND B:B = F2
    A:A = E3 AND B:B = F3
    A:A = E3 AND B:B = F4
    A:A = E3 AND B:B = F5
    A:A = E3 AND B:B = F6

    A:A = E4 AND B:B = F1
    A:A = E4 AND B:B = F2
    A:A = E4 AND B:B = F3
    A:A = E4 AND B:B = F4
    A:A = E4 AND B:B = F5
    A:A = E4 AND B:B = F6

    A:A = E5 AND B:B = F1
    A:A = E5 AND B:B = F2
    A:A = E5 AND B:B = F3
    A:A = E5 AND B:B = F4
    A:A = E5 AND B:B = F5
    A:A = E5 AND B:B = F6

    A:A = E6 AND B:B = F1
    A:A = E6 AND B:B = F2
    A:A = E6 AND B:B = F3
    A:A = E6 AND B:B = F4
    A:A = E6 AND B:B = F5
    A:A = E6 AND B:B = F6

  4. #4
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs with or?

    Quote Originally Posted by Davestar View Post
    Thanks for such a quick response, but the formula doesn't seem to work. I think I've not explained what I am looking for sufficiently clearly.

    The formula you've given calculates the sum of the sumifs as follows:

    A:A = E1 AND B:B = F1
    A:A = E2 AND B:B = F2
    A:A = E3 AND B:B = F3
    A:A = E4 AND B:B = F4
    A:A = E5 AND B:B = F5
    A:A = E6 AND B:B = F6
    It doesn't do anything of the sort! Where in my formula do you see a reference to cells E2, E3, E4, E5 and E6?

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    New Member
    Join Date
    Apr 2007
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs with or?

    Okay sorry, it doesn't. I tried to amend it.

    =SUMPRODUCT(SUMIFS(C:C,A:A,E1,B:B,F1:F6)) does

    A:A = E1 AND B:B = F1
    A:A = E1 AND B:B = F2
    A:A = E1 AND B:B = F3
    A:A = E1 AND B:B = F4
    A:A = E1 AND B:B = F5
    A:A = E1 AND B:B = F6

    if I amend to =SUMPRODUCT(SUMIFS(C:C,A:A,E1:E6,B:B,F1:F6))

    it does:

    A:A = E1 AND B:B = F1
    A:A = E2 AND B:B = F2
    A:A = E3 AND B:B = F3
    A:A = E4 AND B:B = F4
    A:A = E5 AND B:B = F5
    A:A = E6 AND B:B = F6

    I want it to loop through to do all the possible combinations as stated in my second post. Is that possible?

  6. #6
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs with or?

    I see.

    You can either use an array formula**:

    =SUM(SUMIFS(C:C,A:A,TRANSPOSE(E1:E6),B:B,F1:F6))

    or make it so that your two criteria ranges are orthogonal (i.e. one is a single-column array, the other a single-row array): for example, if you make it so that the values in F1:F6 are instead in e.g. F1:K1, then you can use the following, which does not require committing with CSE:

    =SUMPRODUCT(SUMIFS(C:C,A:A,E1:E6,B:B,F1:K1))

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    New Member
    Join Date
    Apr 2007
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs with or?

    That's grand. They both work perfectly. I didn't realise that you needed orthogonal arrays to work the sumproduct/sumifs formulae. Thanks for your help.

  8. #8
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs with or?

    You don't always - only if you're applying multiple OR conditions to two of your criteria_ranges.

    See here if you're interested in a technical explanation:

    COUNTIFS: Multiple “OR” criteria for one or two criteria_Ranges EXCELXOR

    And you're welcome!
    Advanced Excel Techniques: http://excelxor.com/

Some videos you may like

User Tag List

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
  •