DUM dynamic range (based on current date/month)

users
#1

Hi all,

Ok, just one more thing and I'll have this spreadsheet done for my boss...

Ok, 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
Sales Rep
  
Rep 1
  
Rep 2
  
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...

Thanks,

Charles

#2

Hi,
Without trying, so not exact names
If(month(today()) < 8; sum(jan-jul);sum(aug-dez))
Hope this helps...

Liebe Grüße, / Yours,
Florian Reisinger

#3

Hi all,

Ok, we keep sales for Sales by month, with the Sales Rep in a Row and
each month in a column...

Sounds simple, but...

So something like this:

This didn't come through properly (my email client does text by
default, and didn't get an html part, not sure if that is why I'm not
seeing what you intended), and doesn't make any sense. However,
assuming it's as simple as you state above...

I'm picturing each row for a different sales rep, and columns B
through M are for months of the year (with A being for the name). In
which case I assume that each cell has a sales amount if we are past
that month, otherwise has zero?

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...

Uh, I don't get this, maybe because I'm not a financial type, but do
you mean it should always sum Jan to Jul, unless we are in or after
Aug, in which case it should sum Jan to Aug, but it will never sum Jan
to Mar or Jan to Nov or anything else? Or do you simply mean it should
sum Jan to whatever month we've just passed?

Anyone have a clue? I've tried so many different permutations of
SUMIF(), SUM(INDIRECT()), etc, and just can't figure this one out...

Assuming the latter, and that months have a zero if no sales figures
yet, then the formula should simply be "SUM(B1:M1)". The zeros just
won't affect the total.

But that's too simple for you not to have worked out, so I'll go ahead
and assume you don't mean that. Unfortunately I'm not quite sure where I
lost your explanation, so can you clarify where I've missed the point?

Paul

#4

Are you perhaps making heavy weather of this? What is in the cells for months that have not so far occurred? Surely they must be blank or zero: how can the reps have made sales before the month begins? If you sum the values for all the months, the result will become the sum for the year to date as the various monthly values are entered.

I trust this helps.

Brian Barker

#5

Thanks for the reply Paul,

I was working on a 'wtf?' reply as to why the list converted my HTML email (had the values in a simple table) to plain text.

Also, I have no clue what happened with the 'DUM' in the subject - I didn't type that... weirdness...

Blindly converting HTML to plain text AND disallowing small simple attachments really makes it difficult to paint an accurate picture to get help.

Anyway...

Ok, we keep sales for Sales by month, with the Sales Rep in a Row and
each month in a column...

This didn't come through properly (my email client does text by
default, and didn't get an html part, not sure if that is why I'm not
seeing what you intended), and doesn't make any sense. However,
assuming it's as simple as you state above...

It is -

I'm picturing each row for a different sales rep, and columns B
through M are for months of the year (with A being for the name). In
which case I assume that each cell has a sales amount if we are past
that month, otherwise has zero.

Almost... there is also a YTD column at the end, and this is the column with the formula I'm working on.

But, your confusion is my fault, and stems from one important detail I neglected to mention.

I have one sheet for the current year, and other sheets for previous years.

The formula for the YTD column for the current year sheet is just as simple as you described below and works fine.

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.

This is so the boss can easily see how each Rep is doing compared to each prior year (I pull this value in on a Master Sheet used to show these comparisons).

Currently I do a Find/Replace at the beginning of each month to change the formula to sum the correct ranges, but I'd really like this to happen dynamically.

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...

Uh, I don't get this, maybe because I'm not a financial type, but do
you mean it should always sum Jan to Jul, unless we are in or after
Aug, in which case it should sum Jan to Aug, but it will never sum Jan
to Mar or Jan to Nov or anything else? Or do you simply mean it should
sum Jan to whatever month we've just passed?

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...

Thanks again Paul

#6

Hmmm... yeah, I guess I could create a named range for each possible period - jan, jan-feb, jan-mar, etc - then do it ion a nested IF, but I'm hoping to avoid anything so hairy/ugly...

But thanks...

#7

Is that the new MS Office format? :wink: