# Thread: Conditional Sum Array Formula (Excel 2003)

1. ## 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. ## Re: Conditional Sum Array Formula (Excel 2003)

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

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

Steve

3. ## Re: Conditional Sum Array Formula (Excel 2003)

Thanks Steve.

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

4. ## 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
•