+ Reply to Thread
Results 1 to 4 of 4

Array SUM formula with range of conditions

  1. #1
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Array SUM formula with range of conditions

    Hi there,
    I want to know if this can be done, and if yes, IŽll need Your assistance.
    First see attached file with sample data.
    1) Input data are static and helper columns can be added if needed.
    2) Filter will be dynamic range (in attachement is the filter static), and the count of years can be changed on users request. So there could be only 2011, but also 2011+2012, 2012+2013+2014 etc.
    3) In col 'J', the is what I know to do, but I do not want to use SUMIF+SUMIF+SUMIF... for each year (the count of years will change througt time as mentioned above).

    In my oppinion, an array formula has to be created, but I have no idea how to build it.

    So pls, can someone?
    Thanks

    Regards

    M
    Data.xlsx

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Array SUM formula with range of conditions

    Hi,

    No need for array formulas. In K4:

    =SUMPRODUCT(SUMIFS(Data[Shipments],Data[Truck],I4,Data[Year],$G$4:$G$6))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Re: Array SUM formula with range of conditions

    Hi,
    it cant be so simple... :-D

    Many thanks for Your quick reply! Works as needed...

    Regards

    M

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Array SUM formula with range of conditions

    You're welcome!

+ 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. Sum a range of data from an array of conditions
    By beiag001 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2014, 05:31 PM
  2. Array formula with a few conditions
    By Sirishgreen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2009, 07:32 AM
  3. Array formula with 2 conditions...
    By JR573PUTT in forum Excel General
    Replies: 0
    Last Post: 02-16-2006, 04:44 PM
  4. Array formula with 2 conditions...
    By JR573PUTT in forum Excel General
    Replies: 0
    Last Post: 02-16-2006, 04:44 PM
  5. [SOLVED] Tricky array formula issue - Using array formula on one cell, then autofilling down a range
    By aspenbordr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2005, 11:05 AM

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