+ Reply to Thread
Results 1 to 3 of 3

Help using a list/array as criteria in SUM(SUMIFS())

  1. #1
    Registered User
    Join Date
    08-07-2012
    Location
    Auckland
    MS-Off Ver
    Excel 2010
    Posts
    26

    Help using a list/array as criteria in SUM(SUMIFS())

    =SUM(SUMIFS(A:A, B:B, {"X","S"}, C:C, ">="&H1))

    The above formula works fine. But I would like to be able to replace the {"X", "S"} with a cell reference so that I can manipulate the letters without editing formulas directly. Eg:

    =SUM(SUMIFS(A:A, B:B, G1, C:C, ">="&H1)) where G1 contains {"X", "S"}.

    Ideally, I would like to use a formula in G1 to change the list based on some conditions. I have no trouble coming up with the formula for G1, but the main formula won't accept it.

    What am I doing wrong?

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Help using a list/array as criteria in SUM(SUMIFS())

    You can't use {"X", "S"} in a cells. Enter X & S in a range & use SUMPRODUCT.

    ENter G1 = X; G2 = S

    then

    =SUMPRODUCT(SUMIFS(A:A, B:B, G1:G2, C:C, ">="&H1))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    08-07-2012
    Location
    Auckland
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Help using a list/array as criteria in SUM(SUMIFS())

    Thanks for that!

    That's starting to look pretty inefficient, with the sumproduct x sumifs - oh well, I guess that's what I signed up for with such a ridiculous request.

    Anyway, I was wondering if you wouldn't mind explaining how my example can work with a simple sum, but when I change to a reference it needs to be a sumproduct. Just trying to increase my understanding

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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