Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Sum Array Formula (Excel 2003)

    Once upon a time I had a formula that would calculate a Year to Date total based on the value I input in a variable field. For example, If the user enters 6, then the formula would sum the first six months of the year. I believe that this was an array formula that looked something like this:

    {=SUMIF($A$2=1(B1)*$A$2=2(B1:C1)*$A$2=3(B11)}

    Where $A$2 is the input field and the user would enter the month number (1= January, 2=February, etc.) and the values to be summed are in cells B1 through M1. If the user enters a 6 then the result would be the sum of January through June.

    I am struggling to recall how to write this formula in order to get it to work again.

    Any help would be greatly appreciated.

    Bill

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Sum Array Formula (Excel 2003)

    Something like this: (confirm with control-shift-enter):

    =SUM(IF(MONTH(A3:A100)<=$A$2,B3:M100))

    Adjust the ranges as desired.

    Steve

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum Array Formula (Excel 2003)

    Thanks Steve.

    While this wasn't the formula that I remembered it works even better.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Sum Array Formula (Excel 2003)

    In addition, you can replace SUM, with Average, Min, Max, etc to get other stat functions...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •