Totals calculated by date

[ashley0578]

I'm having problems figuring out how to total colums based on date. I've
uploaded my excel file. There are four sheets; unfunded deals, funded
deals, turndowns, and recap. The recap sheet lets my boss know the
totals on the previous three sheets. I need help on how to make
week-to-date totals and month-to-date totals. Thanks in advance!


+-------------------------------------------------------------------+
|Filename: Deal Log2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4520 |
+-------------------------------------------------------------------+

 

[jbrackett]

For MTD you can convert the date (01/24/06 in cell A2) to Month by using

=TEXT(A2,"Mmm-YY") displays as "Jan-06"
=TEXT(A2,"MM/YY") displays as "01/06"

For week, I usually convert it to the week number in Oracle before
bringing it into Excel but you can use the add-in:

WEEKNUM
Returns a number that indicates where the week falls numerically within
a year. If this function is not available, and returns the #NAME? error,
install and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and
then click OK.
If necessary, follow the instructions in the setup program.
Syntax

WEEKNUM(serial_num,return_type)

regards and good luck,

Jay

 

[Guest]

I was unable to access your file..........
If your dates are in column A, and your amounts to sum are in column
B,......then you can type a "from date" in E1 and a "to date" in E2 and use
this........

=SUMIF(A:A,">="&E1,B:B)-SUMIF(A:A,">"&E2,B:B)

hth
Vaya con dios,
Chuck, CABGx3

 

[ashley0578]

Hi Chuck, I've seen that code you've posted in another post and I didn't
really want to actually type the dates in. I'm looking for something
else similar to what I would use in Access like date()-30 or date()-7.
Know what I mean??

 

[Guest]

Maybe something like this............

=SUMIF(A:A,">="&TODAY()-7,B:B)-SUMIF(A:A,">"&TODAY(),B:B)

Vaya con Dios,
Chuck, CABGx3

 

[ashley0578]

That was perfect and worked like a champ. I could have built this in
access but they wanted it in excel and I don't know this program as
well!

 

[Guest]

Glad you got it working.........thanks for the feedback, and welcome to Excel.

Vaya con Dios,
Chuck, CABGx3