I have a spreadsheet that contains information about a lot of customers. All customers invest in the same businesses and the same products. What I want to do is to create an expression or function that would insert a new row under each businesses and sum up all the values. (applies to the entire spread sheet). Anyone know how to do this?

Thanks in advance.

You could use SUBTOTALS. Go to Data > SubTotals and then set up the appropriate columsn that you want summed.

I tried that. I got zeros for my columns. Although, it is all zeros, it gave me a total (zeros) for the customer, but I want more specific, I want the totals of each business investments.

Can you provide a small sample of the data and layout?

Another possibility is to use Pivot Tables. But use a dynamic named range to refer to the underlying data and in the Pivot Wizard, use that defined name. Then you can hit the Pivot refresh button and it will automatically apply changes.

I am still new to both access and excel. I am using this excel file to update an access report. In other word, I linked the excel file to the database. Would creating a pivot table(s), complicate things? If I recall correctly, pivot table has some limitations or restrictions, like it is not updateable.

Sample Data:
Businesses Products Value
Business 1
Products 1
a 5
b 4
c 5
automated sub total here
Products 2
a 7
b 5
c 3
automated sub total here
grand total here

Business 2 Products 1
a 4
b 45
c 5
automated sub total here
Products 2
a 45
b 45
c 223
automated sub total here
grand total here
.

There are many businesses, and many products under each business. I want an automated sum for products, and also a grand sum for the entire business.

Sorry bout the formatting

No, Pivot Table is updateable. However, you need to use the dynamic named range so that when you do click the Refresh button, it will include new data rows/columns.

Name: MyPivot

Refers to (box at bottom of dialog window):

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

This will expand as your data is added. Then in the Pivot Table wizard, when it asks for the range put this in:

MyPivot


===============

Alternative:

Does it have to be under each item? If you want a separate worksheet for the summary, then you can use SUMIF (for one condition) and SUMPRODUCT (for two conditions).

If you provide a small Excel file sample (attach the file through the "Manage attachments"), I can show you how to do that.

Due to circumstances I cannot upload the file nor a screenshot. But if you could go to

Updating Report via Excel

The last message, I attached some screen shot. If you look at the 3rd page, i think that's the screen shot of the excel file.

I need to sum up all the products for each business. (both column F and G). The sum should appear after the last product for each business.

This is a just randomed generated data with the right format. The actual data, for each client - the businesses and products are the same.

Instead of, manually inserting a new row after each business, and under the right column ( F or G) and do =SUM(....). I want to automated.

I am working with like a huge file.

Not sure if this helpful, on the actual file, all the clients have like 40 rows. first client starts at row2, 2nd client start at row 42 .. and onwards. The first business listed under the first client starts let's say row 3, the first business listed under the 2nd client starts row 43. I dont know .. .like you create a new criteria, where every certain (40 in this case) rows... insert a new row..and under specific columns...do a summation of the 4 (depending on the number of products) rows above..for example.

Thanks in advance.

Still not sure about this. Wouldn't be easier to do subtotals in Access?

Anyway, here are a couple of approaches. Each assume that the data is filled in.

Fill Data:

Select cells A3:G28 (in example),
Edit > Goto ... Special button to select blanks.
In formula bar, enter =A3 and hit CTRL+ENTER.
Select the same area, copy and paste special values

then on the attachment are two options:

1. Formula using SUMPRODUCT (I1:K4)

2. Pivot Table, using MyData with dynamic named range set as shown, then using that as basis for Pivot (see screen shot).

I followed your fill data instructions, I am getting REF errors in my spaces.

----
Forget about the subtotals for a second. How do you make it...so that after every certain rows, you insert a new row? (for example, insert a new row after every 2 rows, insert a new row after every 20 rows)

thanks in advance