Conditional Sum Array Formula (Excel 2003)

Topic

New Reply

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(B1:D1)}

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

Reply | Quote

Replies

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

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

Adjust the ranges as desired.

Steve

Reply | Quote

Thanks Steve.

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

Reply | Quote

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

Steve

Reply | Quote