+ Reply to Thread
Results 1 to 5 of 5

SUMIFS with an array

  1. #1
    Registered User
    Join Date
    07-04-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    24

    SUMIFS with an array

    I have a formula that works well, but I need to add a condition to it. Right now, the formula is:

    =SUMIFS($N$2:$N679,$E$2:$E679,">="&DATE(2014,7,1),$E$2:$E679,"<="&DATE(2014,7,31))+SUMIFS($N$2:$N679,$D$2:$D679,">="&DATE(2014,7,1),$D$2:$D679,"<="&DATE(2014,7,31),$J$2:$J679,"*WHD")

    Basically it says to sum the values in column N if the date in column E falls in July, then also sum the values of column N if the date in column D falls in July AND if there is text in column J that matches "WHD"

    What I want to do is add another text value to search for in the last part of the formula. So instead of just searching for WHD, I would also like to search for "Strawberry" as well in the text in column J.
    Converted to English, I want to sum the values of column N if the date in column D falls in July, AND if there is text in column J that matches "WHD" OR "Strawberry."

    How could I add this to the last part of the formula?

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: SUMIFS with an array

    Un-tested

    =SUMIFS($N$2:$N679,$E$2:$E679,">="&DATE(2014,7,1),$E$2:$E679,"<="&DATE(2014,7,31))+SUM(SUMIFS($N$2:$N679,$D$2:$D679,">="&DATE(2014,7,1),$D$2:$D679,"<="&DATE(2014,7,31),$J$2:$J679,{"*WHD","Strawberry"})
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMIFS with an array

    For the 2nd SUMIFS try this...

    +SUM(SUMIFS($N$2:$N679,$D$2:$D679,">="&DATE(2014,7,1),$D$2:$D679,"<="&DATE(2014,7,31),$J$2:$J679,{"*WHD","Strawberry"}))

    You could shorten the formula a bit and make it easier to read if you used cells to hold the date criteria.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    07-04-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    24

    Re: SUMIFS with an array

    Awesome, thanks so much! Those solutions worked, and if I need to add criteria in the future, the expansion possibly is there. Happy 4th of July!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMIFS with an array

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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 Array formula
    By Redcoal in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-19-2014, 11:03 PM
  2. [SOLVED] Array/Sumifs. Dunno which to use.
    By nogstai in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2013, 03:56 AM
  3. SumIfs with array formula
    By mz1378 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-16-2012, 06:03 AM
  4. Array versus. SUMIFS
    By billybobb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-27-2011, 03:56 PM
  5. Array Formula with SumIFS
    By Avinash Beepath in forum Excel General
    Replies: 5
    Last Post: 02-02-2011, 01:45 PM

Tags for this Thread

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