Sum? SumIf? DSum? HELP!!! (1 Viewer)

NESTED FORMULAS WERE LOST WHEN I HAD TO ATTACH AN .XLS FILE BUT THE FORMULAS ARE SPELLED OUT.'

I have attached a sample of the the spreadsheet I inherited from an Excel user. I must recreate the formulas in columns M, N, and O by using an Access 2007 expression. Can someone help me? The Excel formula for

cell M2 is

=IF(MONTH(Period0)=1,P2,IF(MONTH(Period0)=2,SUM(P2:Q2),IF(MONTH(Period0)=3,SUM(P2:R2),IF(MONTH(Period0)=4,SUM(P2:S2),IF(MONTH(Period0)=5,SUM(P2:T2),IF(MONTH(Period0)=6,SUM(P2:U2),IF(MONTH(Period0)=7,SUM(P2:V2),IF(MONTH(Period0)=8,SUM(P2:W2),IF(MONTH(Period0)=9,SUM(P2:X2),IF(MONTH(Period0)=10,SUM(P2:Y2),IF(MONTH(Period0)=11,SUM(P2:Z2),SUM(P2:AA2))))))))))))

cell N2
=IF(MONTH(Period0)=1,A2,IF(MONTH(Period0)=2,SUM(A2:B2),IF(MONTH(Period0)=3,SUM(A2:C2),IF(MONTH(Period0)=4,SUM(A22),IF(MONTH(Period0)=5,SUM(A2:E2),IF(MONTH(Period0)=6,SUM(A2:F2),IF(MONTH(Period0)=7,SUM(A2:G2),IF(MONTH(Period0)=8,SUM(A2:H2),IF(MONTH(Period0)=9,SUM(A2:I2),IF(MONTH(Period0)=10,SUM(A2:J2),IF(MONTH(Period0)=11,SUM(A2:K2),SUM(A2:L2))))))))))))

cell O2
=SUM(A2:L2)

To repeat my suggestion from elsewhere, your data is not normalized, so option one is normalizing the design so you have a database rather than a spreadsheet:

http://www.r937.com/Relational.html

Databases and spreadsheets are different tools; you're trying to use a screwdriver on a nail.

I have unique identifiers, and other fields that I removed just to focus on the data at hand that was giving me trouble. Thanks anyway

My point is that the data at hand is not stored properly for a relational database, if you have months as fields as shown on the spreadsheet. If you're going to use a relational database to store the data, it should be stored in that format, not a spreadsheet's format.

If you insist on keeping it this way, as also suggested elsewhere, you can create a UNION query that "normalizes" the data, allowing you to use methods more native to a database to summarize the data. Trying to work with it as-is will be a never-ending headache.

Paul is right, and you are barking up the wrong tree. Access in not Excel. Show your tables - use the Relations window, expand the tables in full so that all columns are visible, and then tell us what you want calculated -that's hard to divine from some excel formula.