At 13:47 20/07/2014 -0400, Charles Marcus wrote:
... we keep sales for Sales by month, with the Sales Rep in a Row
and each month in a column... So something like this:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD
What I'm trying to do is come up with a formula that can go in the
YTD column that will SUM only Jan thru Jul for each Rep - and
automatically change to SUM Jan thru Aug once we hit 8/1, etc...
Anyone have a clue? I've tried so many different permutations of
SUMIF(), SUM(INDIRECT()), etc, and just can't figure this one out...
At 08:39 21/07/2014 -0400, Charles Marcus wrote:
I have one sheet for the current year, and other sheets for previous
years. [...] The problem is the same column for the prior year
sheets. I need the YTD column for those sheets to sum up each Sales
Reps totals for that past year, but only to the *current* month.
Subsequent months are not zero, so I can't just SUM the entire range.
Actually I want it to sum the range from Jan to MONTH(TODAY())-1.
So, through the end of the prior month. Hope this better defines the problem...
Better, but no cigar. You twice above say to the *current month*
(change to August on 1 August; "current month") and twice suggest the
previous month (MONTH(TODAY())-1; "prior month"). The formula will
differ depending on which you require, of course.
Let's suppose you have the months in columns from B to M. Then try:
The OFFSET() function here takes a reference to Bn - the January cell
- and offsets it by MONTH(TODAY))-1 cells to the right. So today, in
July, this is six cells to the right, or Hn. The SUM() function sums
from Bn to Hn, which is what you require for July.
If you want the sum to the previous month, you could start with:
- but this would not work for January, where you actually need to sum
nothing. You probably need to handle that as a special case by
including an IF() in your expression:
I trust this helps.
To unsubscribe e-mail to: email@example.com
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
- Re: [libreoffice-users] dynamic range (based on current date/month) · Brian Barker
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy