Calculation Sum of Values If Equal to Another Field

[Matt Bowlby]

I have a housing database which lists individual housing units (Unit) with the area (Area 1, Area 2, Area 3, etc.) the building is located and the number of families (1, 2, 3, etc.) housed in the building. I need to create a calculation that gives the total number of families that are associated with Area 1. So, if I have

UNIT AREA FAMILIES

1 1 1

2 1 3

3 1 2

4 2 1

5 3 2

6 3 2

So, the total number of families in Area 1 is equal to 6. How do I calculate this?

Thank you,

Matt.

[comment]

Assuming there is nothing special about Area 1, and the question applies equally to Area 2, 3, etc:

Define a summary field as Total of [Families]. Sort the records by Area and show them in a List layout with a sub-summary part by Area. Place the summary field in the sub-summary part. If you like, you can delete the body part from this layout, so that your report will look like:

Area 1 - 6

Area 2 - 1

Area 3 - 4

Total - 11

[hannie]

This almost solve my problem, except that, mine has a lot of columns and creating a summary field for each column would be very tedious:

I am getting a total of sales per week for 3 years (these are my columns) and my rows are all the stores I have in different areas:

Store / Area / Product Category / Product Code / Wk 1 / Wk 2 / Wk 3 / Wk 4 / ... / Wk 156

A / Z / Heavy / 101 / 1 / 4 / 6 / 7 /...

A / X / Light / 302 / 5 / 2 / 4 / 4 /...

A / Z / Heavy / 101 / 2 / 3 / 1 / 4 / ...

B / S / Heavy / 101 / 3 / 1 / 3 / 2 / ...

B / S / Medium / 201 / 1 / 3 / 6 / 3 / ...

B / S / Medium / 203 / 4 / 2 / 1 / 4 / ...

C / T / Light / 302 / 2 / 3 / 4 / 4/ ...

C / U / Medium / 203 / 12 / 2 / 1 / 3 / ...

I would need to come up with a report with the same weekly columns, but, depending on the filter, say Store & Product Category, would sum up all the weekly sales per Store & Product Category.

Any idea?

[comment]

I am getting a total of sales per week for 3 years (these are my columns)

Properly, you should have a separate record for each weekly amount. If your data is coming in the way you describe, and you only want to summarize it, you could use a repeating calculation field to read the columns and a repeating summary field to summarize them.