defining a range to sum

  • Thread starter Thread starter kate
  • Start date Start date
K

kate

How would i go about summing up until the current selected
month?
I have a spreadsheet with months running across the top,
and projects down the left, i only want to sum the values
for each project up to the current month(this current
month valus is in a cell also on the worksheet - selected
from a pull down).
 
How would i go about summing up until the current selected
month?
I have a spreadsheet with months running across the top,
and projects down the left, i only want to sum the values
for each project up to the current month(this current
month valus is in a cell also on the worksheet - selected
from a pull down).
Click to expand...

If your months are listed as Excel recognized dates and are in B1:M1 and if
your projects are listed in column A with the data in B2:M6, then the
*array-entered* formula:

=SUM(B2:M6*(B1:M1<DATE(YEAR(TODAY()),MONTH(TODAY()),1)))

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron
 
Care to specify the ranges involved along with the cell that houses the
criterion month?

It's also essential how the months are entered "across the top" -- as true
dates or as 3-letter month names, etc.
 
...
...
If your months are listed as Excel recognized dates and are in B1:M1 and if
your projects are listed in column A with the data in B2:M6, then the
*array-entered* formula:

=SUM(B2:M6*(B1:M1<DATE(YEAR(TODAY()),MONTH(TODAY()),1)))
Click to expand...
...

Using SUMPRODUCT rather than SUM would avoid the need for array entry. Also, the
first day of the current month is TODAY()-DAY(TODAY())+1, which uses 2 fewer
function calls and one fewer nesting level. Then again, don't need to add the 1
if you use a <= check. So

=SUMPRODUCT(B2:M6*(B1:M1<=TODAY()-DAY(TODAY())))
 
Using SUMPRODUCT rather than SUM would avoid the need for array entry.
Click to expand...

I've wondered what the difference in execution speed is between using
SUMPRODUCT vs using an array formula. Do you know?


--ron
 
Ron Rosenfeld said:
I've wondered what the difference in execution speed is between using
SUMPRODUCT vs using an array formula. Do you know?
Click to expand...

See the thread beginning at

http://google.com/[email protected]

Note that I don't avoid array formulas because of speed, I avoid them to
avoid typos. Any formula that works entered normally also works entered as
an array formula. The reverse is not true. Therefore, any normal formula
involving no more function calls and no more nesting levels than an
equivalent result array formula should be favored. The more robust the
better.
 
See the thread beginning at

http://google.com/[email protected]

Note that I don't avoid array formulas because of speed, I avoid them to
avoid typos. Any formula that works entered normally also works entered as
an array formula. The reverse is not true. Therefore, any normal formula
involving no more function calls and no more nesting levels than an
equivalent result array formula should be favored. The more robust the
better.
Click to expand...

Interesting discussion at that link. Thank you for posting it.

I think it is due to what I am used to, but I find it easier to "understand"
and debug array formulas than their equivalent SUMPRODUCT variations. But I
will look more closely at the latter.


--ron
 
...
...
I think it is due to what I am used to, but I find it easier to "understand"
and debug array formulas than their equivalent SUMPRODUCT variations. But I
will look more closely at the latter.
Click to expand...

It's a matter of taste. Also, since I try to make may own workbooks
interchangeable between Excel and OpenOffice Calc, I now avoid array formulas
whenever possible.
 
I would use the offset function ie

You have the following data:

Column A B C D
Row 1 Month Number 2
Row 2 Jan Feb Mar YTD
Row 3 15 6 12

If you wanted to sum the YTD totals upto the end of Feb ie month 2 the following formula needs to be entered into cell D3

Sum(Offset(a3,0,0,1,$B$1)) this will return the answer 21.

Hope this helps
 
Back
Top