+ Reply to Thread
Results 1 to 6 of 6

Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays

  1. #1
    Registered User
    Join Date
    12-19-2015
    Location
    Reading, England
    MS-Off Ver
    10
    Posts
    9

    Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays

    Dear Forum,

    In my new role I am analysing data a lot and the COUNTIF and SUMIF functions are my new best friends.

    I am trying to understand the general principle of how to (reliably) build multiple criteria SUMIFS and COUNTIFS formulae, working on multiple criteria columns, using arrays. I thought as long as you didn't create an array structure that went beyond '2 dimensions' it would work, and generally they do. I have been playing around and I list out the results at the bottom of the thread (I don't have permission to attach files).

    What is baffling me is why the COUNTIFS formula below does work, but the SUMIFS formula doesn't, but they have the same 3:1:3 array structure:

    =SUM(COUNTIFS(B:B,{"x","y","z"},C:C,"d",D:D,{"7";"5";"8"}))

    =SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,{"d","g","h"}))

    The formulae are working on the following table:

    colA colB colC colD
    b x d 6
    b x d 7
    c x d 7
    d x d 5
    e x d 7
    f y e 7
    g x e 8

    Experiment Results:

    THESE WORK Array Structure
    =SUM(COUNTIFS(B:B,{"x","y"},C:C,"d",D:D,"7")) 2:1:1
    =SUM(COUNTIFS(B:B,{"x","y"},C:C,"d",D:D,{"7";"5"})) 2:1:2
    =SUM(COUNTIFS(B:B,{"x","y","z"},C:C,"d",D:D,{"7";"5";"8"})) 3:1:3 **
    =SUM(SUMIFS(D:D,A:A,"b",B:B,{"x","y"},C:C,"d")) 1:2:1
    =SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,{"x"},C:C,{"d","g"})) 2:1:2
    =SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,"d")) 3:1:1
    =SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,{"x","y"},C:C,"d")) 2:2:1
    =SUM(SUMIFS(D:D,A:A,{"b","c","d","e"},B:B,"x",C:C,"d")) 4:1:1
    =SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,"x",C:C,"d")) 2:1:1

    THESE DON'T
    =COUNTIFS(B:B,{"x","y"},C:C,"d",D:D,"7") doesn’t have sum
    =SUM(COUNTIFS(B:B,{"x","z"},C:C,"d",D:D,{"7";"5";"8"})) 2:1:3
    =SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,{"x","y"},C:C,{"d","g"})) 3:2:2
    =SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,{"d","g"})) 3:1:2
    =SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,{"d","g","h"})) 3:1:3 **
    =SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,{"x","y","z"},C:C,{"d","g","h"})) 3:3:3
    =SUM(SUMIFS(D:D,A:A,{"b","c"},B:B,{"x","y"},C:C,{"d","g"})) 2:2:2

    Chris

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,111

    Re: Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays

    Sumifs also will work as Countifs if you change the
    =SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,{"d","g","h"})) as
    =SUM(SUMIFS(D:D,A:A,{"b","c","d"},B:B,"x",C:C,{"d";"g";"h"}))
    As you created syntax for countifs
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    12-19-2015
    Location
    Reading, England
    MS-Off Ver
    10
    Posts
    9

    Thumbs up Re: Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays

    That's great! Thanks, What a difference a wee dot makes.

    Just applied it to a real life analysis and after I had remembered to put in the leading sum function it works a treat. I guess trial and error will teach me what the limit on the various array formats is.

    The solution given is for a specific example: I really want to know what the general rule is when building sumifs/countifs on combinations of multiple criteria on multiple columns. What works, what doesn't and when to use ; or , .
    Last edited by cmbh; 01-10-2016 at 10:20 PM.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,159

    Re: Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays

    The solution given is for a specific example: I really want to know what the general rule is when building sumifs/countifs on combinations of multiple criteria on multiple columns. What works, what deposit and when to use ; or , .
    Perhaps this can help. If you aren't aware of it comma separators in arrays are understood to be column separators, and semi-colons are understood to be row separators. When presented column to column there is an implicit AND criteria. When presented "cross-wise" or transposed column to row there is an implicit OR criteria.

    One way to see this in action would be to highlight / select the SUMIFS part in the formula bar in each of the formulae and press the F9 function key. You will see the arrayed results in each case. This has helped me to follow the logic.

    Another way would be to make the formula in question the active cell, click on Formulas, click Evaluate Formula in the Formula Auditing group and then click Evaluate repeatedly in the dialogue window. Watch the "magic".

    Has this helped?
    Dave

  5. #5
    Registered User
    Join Date
    12-19-2015
    Location
    Reading, England
    MS-Off Ver
    10
    Posts
    9

    Re: Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays

    Thanks. That helped!

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,159

    Re: Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays

    Glad it helped, cmbh. Thank you for the feedback and the rep.

+ 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] Countifs and Sumifs for multiple criteria
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2015, 02:48 PM
  2. [SOLVED] SUMIFS/COUNTIFS array function question (multiple criteria)
    By akamenov88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2015, 07:08 PM
  3. [SOLVED] Using Name Arrays as Criteria in SumProduct/SumIfs
    By stepscot in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-16-2014, 10:24 AM
  4. [SOLVED] sumifs, vlookup, multiple column rules
    By will.girling in forum Excel General
    Replies: 2
    Last Post: 03-11-2014, 10:54 AM
  5. SUMIFS using arrays as criteria
    By Ricardo Mass in forum Excel General
    Replies: 5
    Last Post: 02-28-2014, 12:36 PM
  6. [SOLVED] Countifs arrays with multiple criteria and subtotal function
    By 5150 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-12-2013, 01:58 AM
  7. Multiple criteria arrays in countifs function resulting in incorrect answer
    By laurenmoney in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-28-2013, 12:57 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