sum more than 5 cells

[Guest]

if sum more than 5 cells not in consecutive order, I can use the AutoSum and
select the cells I want using the control button or type in =SUM(C23,C34,c40,
c45, c50,c56,c63,...). However, I receive an error saying I have too many
arguments. Why is this displaying?

 

[JE McGimpsey]

Because you have too many arguments.

XL functions are limited to 30 arguments.

One workaround: Select your cells and name them, say "sum_range" by
entering "sum_range" (without quotes) in the Name box at the left side
of the formula bar.

Then you can use one argument:

=SUM(sum_range)

 

[Guest]

Same problem, but I want to sum every 5th line (Multibles the of 30 max) from
spreadsheet imported from "Home Depot's WorkSite CD". None of the functions
came over, just the resultant values.

 

[Myrna Larson]

Assuming you want to sum rows 1, 6, 11, etc

=SUMPRODUCT((MOD(ROW(A1:A100),5)=1)*(A1:A100))

To sum rows 5, 10, 15, etc, change =1 to =0

 

[Guest]

Thank you, Myrna. This spreadsheet is a material list that repeats it's
format/layout every 5th line. Can I set up a sheet that will have functions
automatically duplicated every 5th line for each material entry? 1st line
blank.
Next 2 lines are just descriptions (text),
4th is (B-hourly rate) (C-hours) (E-material cost) (F-labor cost)
(G-equipment cost) (H-total unit cost).
5th line (A-# of units) (C,E,F,G,H as above * # of units).
These are then totaled at bottom (Previous question you answered)
thanks

 

[Myrna Larson]

Set up the first 5 rows as you want them (say rows 2:6). Then select all 5
rows and Edit/Copy. Then select a multiple of 5 rows below that (say 7:46 for
an additional 8 blocks) and Edit/Paste.

 

[Guest]

Thanks again, Myrna. this would be ok if i were manually entering the
material one at a time. I am importing an existing table from another
program into excel. All the text and values transfer, but none of the
formulas. I have 400 lines so far in this table & it will grow as I enter
more materials using the original program. I would like to the update the
excell table with the new entries form the first program as more materials
are identified. Can i use some kind of macro to replace the values in the
excel table copied from the original program with formulas en masse? I'm
downloading this table to my pocket pc for use on the road.
Thank you. Bill

 

[Myrna Larson]

YOu want to replace literal values with formulas? Usually people want to go
the other way. It probably is possible to work up a macro, but not without
lots more information about the details of what you want to do, the layout of
the two files, etc.

 

[Guest]

Yes, replace values with formulas. this allows me to manipulate data on the
road to create a new list from the master. The program I'm starting with is
one that "The Home Depot" store's Contractor Services gives to contractors
(me) to look up, tabulate, & order merchandise. It's created by "The
Craftsman Book Co." (www.costbook.com). Once I have selected each item, I
may input how many of each & change it's cost and rate & Hours for installing
that item. Hit return & it loads it into a form, taking up 5 lines per item,
& gives running totals at the bottom (my 1st question). When I save it as a
tabbed table, call it into excel, it only transfers the text and numeric
values, not the formulas that allow me to change those values. So you can
see what this looks like, can I send or post this somewhere. Oh, this
Craftsman program does download this to Quickbooks, but I don't have it. I
have excel. Don't mean to use up sooooo much of your time, but I'm lost &
inexperienced at excel. If you can provide me with followup, I would greatly
appriciate it. If not, thanks for your help.
Bill