+ Reply to Thread
Results 1 to 7 of 7

Array formula

Hybrid View

  1. #1
    Registered User
    Join Date
    02-05-2010
    Location
    St. Louis, Mo
    MS-Off Ver
    Excel 2007
    Posts
    2

    Array formula

    Happy New Year everyone.

    I'm working on a spreadsheet designed to track my personal finances on a weekly basis. See the attached file. I want the formula to look at the master list of transactions on the left side of the spreadsheet and display the total debit amount for a particular category if it is within a specific date range. I'm using the array formula {=SUM(($A$2:$A$16<=I$5)*($B$2:$B$16="Mortgage")*($D$2:$D$16))} but I can't get it to look at only the date range that corresponds to the specific week.

    I'm open to using other formulas if there is something else that will work but in my research the array formula seemed to be the best suited. Any help would be much appreciated and if you need clarification feel free to message me back.

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Array formula

    Just a cell reference typo, I think.

    I changed the formula in G3 to:

    =SUM(($A$2:$A$16<=G$2)*($A$2:$A$16>=F$2)*($B$2:$B$16=F3)*($D$2:$D$16))

    And copied that to the other cells, and it seemed to work fine.

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Array formula

    Hi mkhagerty,

    Welcome to the forum and have a lovely year ahead.

    Have a look at the attached file where I have tried solving your problem. Let me know if this works for you.

    Cheers

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Array formula

    Using your posted workbook...
    These regular formulas return the total activity for the referenced Description that is between the referenced start and end dates:

    For Excel 2007+
    G3: =SUMIFS($D:$D,$B:$B,F3,$A:$A,">="&F$2,$A:$A,"<="&G$2)
    For Excel 2003
    G3: =SUMPRODUCT(($B$2:$B$1000=F3)*($A$2:$A$1000>=F$2)*($A$2:$A$1000<=G$2)*$D$2:$D$1000)
    Copy either formula down as far as you need and into the other summary columns.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Array formula

    How about?

    =SUMPRODUCT(--($A$2:$A$16>=F$2),--(A2:A16<=G$2),--($B$2:$B$16=F3),$D$2:$D$16)

    copied down.

    Then you can copy G3:G14 and paste to other tables.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  6. #6
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Array formula

    Heh, I just came back to this thread to post the SUMPRODUCT alternative, but the big boys have been me to it

  7. #7
    Registered User
    Join Date
    12-28-2011
    Location
    India
    MS-Off Ver
    2003, 2007 and 2010
    Posts
    34

    Re: Array formula

    Hi,

    By looking at your profile, I could understand that you are using excel 2007 version, since you are using 2007 version. Your solution can be arrived very easily by using SUMIFS solution.

    Check out the attachment and let me know your thoughts.
    Attached Files Attached Files
    Hari
    "Trying to find excel boundaries"

+ 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