Formula help needed desperately quickly!

sean69

New Member
Joined
Oct 3, 2006
Messages
22
Hi,
I need some help with creating a formula in Excel. Can someone tell me if there is a cap on how many cells you can tell a cell to pick up in a formula? I’m trying to create a formula in a cell in the front sheet of a workbook that picks up the corresponding cells in back sheets in the same book, but as soon as I’ve entered 30 an error message comes up saying ‘you have entered too many arguments for this function’…….
Can anyone tell me how I can enable it to let me add more?
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
Guessing your using the Concatenate function,
Try Concatenating upto thirty cells into a hidden cell (eg white font) do this till you have all data concatenated into a few cells, then in your original cell use Concatenate to the new hidden cells.
 

Erdinç E. Karaçam

Board Regular
Joined
Sep 23, 2006
Messages
202
Generally argument limits depends on a formula.

For example;
For CONCATENATE: limit is 29 Arguments.
For IF : limit is 7 If Arguments.

Please tell more about your formula.
 

sean69

New Member
Joined
Oct 3, 2006
Messages
22
I'm not sure I know any more to say about my formula. Like I say it's in a cell in a front sheet that contains totals from backsheets, so in the cell I am typing: =SUM('Atkins'!C7,'Harpers'!C7....etc)
The only viable option for me is to try and find a way of adding more than 30 into the formula - can this not be done?
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749

ADVERTISEMENT

Sum function is limited to 30 arguments. But you can try to use + in between e.g.

Sum(cell1, cell2...cell30)+sum(cell31,cell32...etc).
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Sean

It really would help if you told us more about what you want to do.

If you want to add up C7 from every sheet in a workbook you could use something like this.

=SUM(First:Last!C7)

Replace First & Last with the names of the first and last worksheets.
 

sean69

New Member
Joined
Oct 3, 2006
Messages
22

ADVERTISEMENT

Sean

It really would help if you told us more about what you want to do.

If you want to add up C7 from every sheet in a workbook you could use something like this.

=SUM(First:Last!C7)

Replace First & Last with the names of the first and last worksheets.
I have just tried doing this and it's not letting me do it, do you know exactly what I need to put in terms of position of apostrophes etc? Sorry I can't elaborate more but I'm not really good with computers and technical jargon so I'm not really sure what else to tell you?

Patsys, thanks, just tried that too but it's not letting me do that either?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Sean

Could you please tell us what you actually want to do?
 

sean69

New Member
Joined
Oct 3, 2006
Messages
22
I’m not sure how else I can put it than in my opening post: I want to add a formula into a cell on the front sheet of a workbook, let’s call this cell C7, for example.
I want the formula to add the sum total of all of the figures in the corresponding cells (C7) in the back sheets in the workbook. Each of these back sheets have a name, e.g. Atkins, Harpers etc
So I am typing in the formula ‘Atkins’!C7,’Harpers’!C7….
But when it reaches thirty, it won’t allow any more.
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
I have just tried doing this and it's not letting me do it, do you know exactly what I need to put in terms of position of apostrophes etc? Sorry I can't elaborate more but I'm not really good with computers and technical jargon so I'm not really sure what else to tell you?

Patsys, thanks, just tried that too but it's not letting me do that either?

Can not imagine why it is not letting you do it. Can you post you sheet so we could all see?

Norie's suggestion is probably better if you have all the sheets next to each other. You do not have to type the expression in the formula, all you need to do is type "=sum(" then go to cell C7 of the first sheet, then while pressing SHIFT, click on the last sheet then ENTER.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,354
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top