+ Reply to Thread
Results 1 to 10 of 10

SUMIFS w/ INDIRECTS?

  1. #1
    Registered User
    Join Date
    08-18-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    SUMIFS w/ INDIRECTS?

    I'm trying to create a SUMIFS formula by getting data based on unique cell values.

    The following formula grabs what I need but instead of "=BNG0726" I would like to use a value in a cell so that the formula changes based on an entered value.

    Also I would like to add a date criteria the same way. For example if date is greater or equal to a value in a particular cell and less than or equal to a value in another cell it will grab the data in cell DATA K1:K100000.

    Here's my formula below. It grabs the data I need but would have to be manipulated to reference the correct account code. Also I couldn't get the date criteria to work.

    =SUMIFS(DATA!$K$1:$K$100000,DATA!$B$1:$B$100000,">629999",DATA!$B$1:$B$100000,"<640000",DATA!$A$1:$A$100000,"=BNG0726")

    Thanks for your help

    Here's my attempt to add date criteria but it didn't produce the correct result.

    =SUMIFS(DATA!$K$1:$K$100000,DATA!$B$1:$B$100000,">629999",DATA!$B$1:$B$100000,"<640000",DATA!$A$1:$A$100000,"=BNG0726",DATA!$D$1:$D$100000,"">="&DATE(4/1/2012)",DATA!$D$1:$D$100000,""<="&DATE(3/31/2013)")

  2. #2
    Forum Contributor wakeupcall's Avatar
    Join Date
    02-29-2012
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: SUMIFS w/ INDIRECTS?

    Assuming 629999 is in A1, 640000 is in B1 and BNG0726 in in C1,


    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: SUMIFS w/ INDIRECTS?

    A variation, think you could also try using sumproduct [but use the smallest explicit range]:

    =SUMPRODUCT((Data!$A$1:$A$1000=$A2)*(Data!$B$1:$B$1000>$A3)*(Data!$B$1:$B$1000<$A4)*(Data!$D$1:$D$1000>=$A5)*(Data!$D$1:$D$1000<=$A6),Data!$K$1:$K$1000)

    where the relevant specs are input into A2:A6
    (real dates are presumed specified in A5 and A6)
    and the desired SumRange is col K

  4. #4
    Registered User
    Join Date
    08-18-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: SUMIFS w/ INDIRECTS?

    Thanks Max! That worked well. Is there a max range for SUMPRODUCT?

    How do I change to resolved?
    Last edited by HCBalelo; 08-18-2012 at 07:30 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: SUMIFS w/ INDIRECTS?

    welcome, glad to hear. you could express your satisfaction with the answer by clicking the little star at the bottom left of my response

    > .. Is there a max range for SUMPRODUCT?
    well, the practical reason why its always best to use the smallest explicit range is simply to optimize calculation/recalc speed, as the sumproduct function is v.calc-intensive in nature. The greater the range covered, the heavier the calc load.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: SUMIFS w/ INDIRECTS?

    Max, why suggest sumproduct?

    Sumifs can be used if it is written correctly and is far more efficient.

    =SUMIFS(Data!$K:$K,Data!$A:$A,$A2,Data!$B:$B,">"&$A3,Data!$B:$B,"<"&$A4,Data!$D:$D,">="&$A5,Data!$D:$D,"<="&$A6)

  7. #7
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: SUMIFS w/ INDIRECTS?

    Actually why the Q? If you read my response carefully, it didn't hint on the SP option being superior. Anyway, this is one good reason - Sumproduct works across all Excel versions, unlike SUMIFs. If one routinely exchanges Excel files with others in the course of work - where various lower Excel versions (especially Excel 2003) are still in active use, its always safer to use SP. Um, another reason, my Excel 2003 doesn't have the new function SUMIFs

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: SUMIFS w/ INDIRECTS?

    Actually it was more a case of wondering why you deviated from sumifs when the OP was already using it, which would indicate that backwards compatibility would not be necessary.

  9. #9
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: SUMIFS w/ INDIRECTS?

    Granted, but I don't think my suggesting the SP option in this thread caused any harm to the OP. Its always good to know of alternatives.

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: SUMIFS w/ INDIRECTS?

    Quote Originally Posted by HCBalelo View Post
    =SUMIFS(DATA!$K$1:$K$100000,DATA!$B$1:$B$100000,">629999",DATA!$B$1:$B$100000,"<640000",DATA!$A$1:$A$100000,"=BNG0726",DATA!$D$1:$D$100000,"">="&DATE(4/1/2012)",DATA!$D$1:$D$100000,""<="&DATE(3/31/2013)")
    Probably better to use cell references for the dates as you already suggested but the problem, specifically, with this formula is the date criteria, you need to use DATE function like this:

    ">="&DATE(2012,4,1)
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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