Sum in a calendar grid, based on current date

[doomy304]

Hello all,

There is an old spreadsheet at my job, not set up by me. Real simple. It has a "calendar" grid (rows are months, columns are days, so the entire month fits on one row). Numerical values go in the grid, and at the end of the row (month) it sums the values and does some other basic calculations based on that.

What I need to get this to do (and I've tried using Access but I think that is outside my scope and would just be more difficult for my coworkers to get a handle on anyway), and what I am stuck on, is how to get it to calculate a sum of all values that are up to one year ago, from today. Basically, if today is 2/22/12, it would sum all values 2/23/11 through 2/22/12 and spit that number out.

I am thinking array, and I am thinking vlookup, but I don't know much about either, and I'm having trouble translating what I am reading about them to the sum problem. Please help if you can

[Brian from Maui]

Care to post some sample data and expected results?

A guess.........

=SUMPRODUCT(--(B1:B100>=F1),--(B1:B100<=F2),C1:C100)

Where B1:B100 house dates, F1 start date, F2 end date, and C1:C100 range to sum.

[doomy304]

Sure thing:

The way it looks now
What I want it to do

Let me know if you need further info.

I'm willing to change the design/layout within reason - this spreadsheet is used by several of us and I'm afraid to remove the "calendar" setup entirely, though.

[Brian from Maui]

Can't access your linked pages, I'm at work.

Try using this to post an example

http://www.excel-jeanie-html.de/index.php?f=1

Have you tried to adapt the generic formula provided?

[doomy304]

OK, had trouble getting it to paste - spreadsheet was too big, but here is a snippet. Pretend the top row has digits across the top (1, 2, 3, etc) signifying day of the month.

First of the yellow columns at the end is "sum for this month", second one is "running total of sums for the past 12 months", third one is "480 - running total."

What I want it to do is stop calculating sum by the month and start calculating it by the DAY. So basically the three farthest right columns would be deleted and replaced with a field at the bottom with sum for the previous 365 days or something like that. But I can't figure out how to get Excel to figure out which cells correspond to the correct dates. I did mess with your formula but I wasn't sure how to translate that to the current calendar "grid" setup.

Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

7

*

JUN

*

*

*

*

*

8.00

*

*

*

*

*

*

*

*

*

*

*

*

*

*

4.00

*

1.50

*

0.50

*

*

1.50

5.50

*

*

21.00

170.50

309.50

8

*

JUL

9.00

*

*

*

1.50

2.50

*

2.00

4.00

*

*

*

*

*

9.00

4.00

*

*

1.50

3.00

*

*

*

*

*

1.00

*

*

9.00

4.00

*

50.50

221.00

259.00

9

*

AUG

9.00

*

2.00

*

*

*

*

*

*

*

*

4.25

*

*

2.50

9.00

*

*

*

*

*

*

*

*

*

*

*

*

8.00

*

8.00

42.75

263.75

216.25

10

*

SEP

*

1.50

*

*

*

*

*

*

*

*

*

8.00

*

*

1.75

*

*

*

*

*

*

*

*

*

*

3.00

8.00

8.00

8.00

8.00

*

46.25

310.00

170.00

11

*

OCT

*

*

8.00

8.00

8.00

8.00

8.00

*

*

*

*

*

*

*

*

*

4.50

*

*

*

8.00

*

*

4.50

3.50

*

2.00

*

*

*

8.00

70.50

380.50

99.50

12

*

NOV

8.00

8.00

8.00

*

*

*

*

6.00

*

*

*

*

*

*

*

*

4.50

8.00

*

*

4.50

*

2.00

*

1.75

*

*

*

*

*

*

50.75

431.25

48.75

13

*

DEC

8.00

8.00

*

*

8.00

8.00

7.00

*

*

*

*

*

*

*

*

*

*

*

*

3.50

*

*

*

*

*

6.25

*

*

*

*

*

48.75

480.00

0.00

14

##

JAN

*

1.75

*

*

*

*

*

*

8.00

*

3.50

8.00

*

*

*

*

*

*

*

*

*

*

2.75

*

*

*

*

*

*

*

*

24.00

480.00

0.00

15

*

FEB

8.00

4.50

8.00

*

*

*

*

*

*

2.00

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

22.50

479.50

0.50

16

*

MAR

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

0.00

458.50

21.50

Spreadsheet Formulas

Cell Formula AH7 =SUM(C7:AG7) AI7 =SUM(AH2:AH7) AJ7 =480-AI7 AH8 =SUM(C8:AG8) AI8 =SUM(AH2:AH8) AJ8 =480-AI8 AH9 =SUM(C9:AG9) AI9 =SUM(AH2:AH9) AJ9 =480-AI9 AH10 =SUM(C10:AG10) AI10 =SUM(AH2:AH10) AJ10 =480-AI10 AH11 =SUM(C11:AG11) AI11 =SUM(AH2:AH11) AJ11 =480-AI11 AH12 =SUM(C12:AG12) AI12 =SUM(AH2:AH12) AJ12 =480-AI12 AH13 =SUM(C13:AG13) AI13 =SUM(AH2:AH13) AJ13 =480-AI13 AH14 =SUM(C14:AG14) AI14 =SUM(AH3:AH14) AJ14 =480-AI14 AH15 =SUM(C15:AG15) AI15 =SUM(AH4:AH15) AJ15 =480-AI15 AH16 =SUM(C16:AG16) AI16 =SUM(AH5:AH16) AJ16 =480-AI16

Excel tables to the web >> Excel Jeanie HTML 4

[Brian from Maui]

I'd start using true date values in a column with corresponding values. Then you can use a Pivot Table to sum a YTD total or even Subtotals in a filtered list.

The table you provided is easy to read but I can't think of a way, without true date values to sum 365 days.

Hopefully someone else can provide an answer using the table you provided.

[doomy304]

Just thought I would follow up on this, we had a system update last night for one of our other systems that unbeknownst to me is now auto-calculating this value exactly the way I wanted it to. Negates the entire need for the spreadsheet really, and makes my life approximately a bazillion times easier.

So thanks for your help, issue resolved.