Find Month to Date data - from weekly data

[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

 

[Frank Kabel]

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

 

[Ron Rosenfeld]

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!

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