+ Reply to Thread
Results 1 to 6 of 6

SUMIFS using arrays as criteria

  1. #1
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    SUMIFS using arrays as criteria

    Hi everybody,

    The below formula is working fine:

    =SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000

    but if I try to add in a second criteria for Region, as below:

    =SUM(SUMIFS(YTD_GBP,Region,{"Central Budgets","Corporate Services"},Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000

    I get zeros.

    But if I do two separate formulas and add them:

    =(SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))+SUM(SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"})))/1000

    I get the correct answer.


    Does anyone know why this is? Are you not able to have two criteria with arrays in a formula?

    Thanks
    Rich
    Attached Files Attached Files
    There must be a quicker way...

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,556

    Re: SUMIFS using arrays as criteria

    Hi,

    Change the comma to a semi-colon in your array: {"Non Billable";"Central Budget"}

    Effectively you need to transpose this range (TRANSPOSE({"Non Billable","Central Budget"}) would equally work, though no need to call an extra function).

    Regards
    Click * below if this answer helped

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

  3. #3
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: SUMIFS using arrays as criteria

    If you do not transpose one array your formula will calculate:
    =SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,"Non Billable")
    +SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,"Central Budget")
    /1000

    If you transpose, calculation will be:
    =SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,"Non Billable")
    +SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,"Non Billable")
    +SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,"Central Budget")
    +SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,"Central Budget")
    /1000
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  4. #4
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: SUMIFS using arrays as criteria

    Perfect, thank you both very much.

    Not sure I quite understand why this works though, is it because I am effectively multiplying matrices together?

  5. #5
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: SUMIFS using arrays as criteria

    It is similar, yes - because arrays are different shapes, every combination must be calculated.

  6. #6
    Registered User
    Join Date
    07-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    77

    Re: SUMIFS using arrays as criteria

    I see, thank you for the help guys, that was a real head-scratcher

+ 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] Sumifs with criteria
    By bigband1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2014, 08:44 AM
  2. Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  3. SUMIFS vs. arrays
    By djapigo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-30-2012, 01:39 AM
  4. Replies: 5
    Last Post: 09-07-2011, 11:14 PM
  5. Replies: 1
    Last Post: 05-16-2011, 05:00 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