Find Month to Date data - from weekly data

  • Thread starter Thread starter miker1999
  • Start date Start date
M

miker1999

Hello,
I would like to find out the Month to Date total on my data. Here i
the set up:

E3:BD3 = Weekending dates (starting 01/09/2004)
E5:BD5 = # of Visitors per week

I would like to find out # visitors Month to Date.

Thank you
 
Hi
if your date is stored in A1 try the following
=SUMPRODUCT(--(MONTH(E3:BD3)=MONTH(A1)),E5:BD5)

note: this will give slightly wrong results if a week starts in Month A
but ends in month A+1. The # visitores are then summed in the A+1 month
 
Hello,
I would like to find out the Month to Date total on my data. Here is
the set up:

E3:BD3 = Weekending dates (starting 01/09/2004)
E5:BD5 = # of Visitors per week

I would like to find out # visitors Month to Date.

Thank you!
Click to expand...


You really can't do it accurately because of how you have your data layed out.
For example, your data for the week ending 3/5 will include visitors from 2/28
and 2/29.

If, instead of "month to date", you redefine that to indicate something that
coincides with the above, then here's one way if it coincides with the way you
wish to have things layed out:

E7: =SUMIF($E$3:E3,">"&DATE(YEAR(E3),MONTH(E3),0),$E$5:E5)

Fill right (copy/drag) to BD7. The formula will adjust automatically.

====================
If you truly want month-to-date figures, you are going to need to have daily
data someplace.

A formula like the following will, with the same caveats as before, give you a
"pseudo" MTD total for the current month:

=SUMIF(E3:BD3,">"& DATE(YEAR(TODAY()),MONTH(TODAY()),0),E5:BD5) -
SUMIF(E3:BD3,">"&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),E5:BD5)


--ron
 
Back
Top