+ Reply to Thread
Results 1 to 13 of 13

Sumif formula on an array of criteria?

  1. #1
    Registered User
    Join Date
    01-15-2010
    Location
    Scottsdale
    MS-Off Ver
    Excel 2010
    Posts
    72

    Sumif formula on an array of criteria?

    I am curious if there is a formula that will return the sum of all values that meet multiple criteria. I have attached an example and while I know that there are a couple of options using sumif() formulas with a single critera, I am looking for a solution where I can have an array or mutliple critera to return the sum of. The example attached should give you the general idea but my real life example has much more data so I could nont simpily string 5 sumif formulas together to return me desired outcome. Let me know if you have any suggestions or need further explaination of what I looking for.

    Thank you,
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sumif formula on an array of criteria?

    Your profile says 2003, but your attachment extension is 2007+.

    For 2007+ use =SUMIFS(sum_range,criteria_range,criteria,...)
    HTH
    Regards, Jeff

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sumif formula on an array of criteria?

    This formula will let you sum the quantity for a range of criteria

    =SUMPRODUCT(SUMIF(Qty!$A3:$A17,Summary!A3:A7,Qty!B3:B17))
    Audere est facere

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumif formula on an array of criteria?

    DaddyL, I don't think yours works for what I think Dail1 wants (?).
    But if you change it from =SUMPRODUCT(SUMIF(Qty!$A3:$A17,Summary!A3:A7,Qty!B3:B17))
    to =SUM(SUMIF(Qty!$A3:$A17,Summary!A3:A7,Qty!B3:B17)) I think that'll work (again, unless I'm misunderstanding the request).
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Registered User
    Join Date
    01-15-2010
    Location
    Scottsdale
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Sumif formula on an array of criteria?

    Sum array example2.xlsxSum array example2.xlsx
    Quote Originally Posted by jeffreybrown View Post
    Your profile says 2003, but your attachment extension is 2007+.

    For 2007+ use =SUMIFS(sum_range,criteria_range,criteria,...)
    Thanks for the heads up on the old profile info.

    I have been playing around with sumifs() but I can't get if to work properly. Please see the new attachment and let me know if I am entering the correct formula. Essentially I would like the formula in this example to = the sum of all the numbers in wk1.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Sumif formula on an array of criteria?

    Hi Dial1,

    If you look at post #3, put that formula in B8 and it returns the total of 493. Not sure why you couldn't just use, =SUM(B3:B7)

    Also, in B2 you did not apply the absolutes to the formula completly...

    =SUMIF(Qty!$A$3:$A$17,Summary!$A3,Qty!$B$3:$B$17)

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sumif formula on an array of criteria?

    Quote Originally Posted by Sambo kid View Post
    DaddyL, I don't think yours works for what I think Dail1 wants (?).
    But if you change it from =SUMPRODUCT(SUMIF(Qty!$A3:$A17,Summary!A3:A7,Qty!B3:B17))
    to =SUM(SUMIF(Qty!$A3:$A17,Summary!A3:A7,Qty!B3:B17)) I think that'll work (again, unless I'm misunderstanding the request).
    Hey Sambo kid! Isn't that the same thing except SUM instead of SUMPRODUCT? The two formulas will do the same thing, I only used SUMPRODUCT because it avoids "array entry"

    Quote Originally Posted by jeffreybrown View Post
    Not sure why you couldn't just use, =SUM(B3:B7)
    Exactly Jeff! That what I was thinking......

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumif formula on an array of criteria?

    Don't want to argue with someone who I think (scratch that, KNOW) has a lot more expertise than I have but the only reason I questioned it was because I tried yours on Dial1's data and got very different results than I got when I put sum in place of sumproduct.

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sumif formula on an array of criteria?

    Quote Originally Posted by Sambo kid View Post
    Don't want to argue with someone......
    No problem - I like a good argument.....

    I got 493 using SUMPRODUCT. If you use SUM and array enter the formula I think you should get the same thing, both are valid approaches

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumif formula on an array of criteria?

    This is where I had a different idea of what Dial1 wanted. When I used sumproduct I got 493 for week one for A (too). But I assumed Dial1 wanted the sum of A's for week 1 to be 22 which is what I got if I went down week 1 and added the #s for A together. So using sum got me 22 but sumproduct got 493.
    So that is why I recommended Dial1 tweek your formula. Just trying to clarify.

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sumif formula on an array of criteria?

    OK, yes we are talking at cross purposes.

    My intention with the formula I posted, i.e.

    =SUMPRODUCT(SUMIF(Qty!$A3:$A17,Summary!A3:A7,Qty!B3:B17))

    ...was the formula to be entered just once to get the quantities for all 5 criteria added together, "A", "B", "C", "D" and "E"

    If you use the same formula with SUM in place of SUMPRODUCT (without array entering) then the formula actually returns an array of values but the value you see displayed depends on where you enter it. If you enter in B3 and copy down you get the 5 quantities in B3:B7. If that's the requirement then it would be better to use the simple SUMIF that Jeff suggest in post #6

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumif formula on an array of criteria?

    Thanks for the explanation, maybe my reading comprehension isn't what it used to be.
    But I saved the formulas you put in for my (possible) future use.

  13. #13
    Registered User
    Join Date
    01-15-2010
    Location
    Scottsdale
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Sumif formula on an array of criteria?

    Thank you all. Worked like a charm!!

+ 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] Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  2. Array Formula w/ Multiple SumIf Criteria
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 07:05 AM
  3. [SOLVED] Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  4. [SOLVED] Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  5. Array Formula w/ Multiple SumIf Criteria
    By Andy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2005, 04:05 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