Excel - adding up more than 300 cells - getting an error message

[Kate2008]

Hello,

Hope all is well.
I am currently putting together an excel sheet which contains ad spending for 3 competitors and my client's spending in 300 citities in the US.

Below is a small sample of what the sheet looks like. At the very end where is says, "Overall Competitor Spending" I am using the sum function to add up all the "Total Market Spending" for each city. I do this by clicking SUM and then going to each cell that contains the total market spending by city using the clt and left click buttons to add the cells to the function but I get an error that says, "formula you typed contains an error". Is it becuase I am adding to many cells?

Hope this makes sense.

Competitor Market Jan-08 Feb-08
Competitor 1 New York, NY $0 $0
Competitor 2 New York, NY $0 $0
Competitor 3 New York, NY $0 $0
Competitor 4 New York, NY $0 $0
Total Market Spending $0 $0
Total My Client $0 $0

Competitor 1 Los Angeles, CA $0 $4272
Competitor 2 Los Angeles, CA $0 $0
Competitor 3 Los Angeles, CA $0 $0
Competitor 4 Los Angeles, CA $0 $0
Total Market Spending $0 $0
Total My Client $0 $0

Competitor 1 Chicago, IL $0 $0
Competitor 2 Chicago, IL $0 $0
Competitor 3 Chicago, IL $0 $0
Competitor 4 Chicago, IL $0 $0
Total Market Spending $0 $0
Total My Client $0 $0

Overall Competitor Spending $0 $4,272
Overall Client Spending $0 $0

 

[dgillz]

Are you really adding as in a1+a2+a3....

Or are you using the sum() function?

You did not say what version of excel you are using but there is a limit to the number of characters in a formula. So what are you up against? How about posting the formula?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[AnotherHiggins]

Please post the formula that is giving you the error. That's the only way we can say for sure what is wrong with it.

Instead of using Sum and manually selecting cells, I think it would be better to do use SumIf.

Something like:
[tab]=SumIf($A2:$A21, "Total Market Spending", D221)

But it would be much better still if you slightly change your format and use a Pivot Table to do all the calculations for you in about 2 seconds.

If you get rid of all your current sub totals and drop month down into it's own single column heading you'll wind up with something that looks like this:
[tt]
Competitor Market Month $ Spent
Competitor 1 New York, NY Jan-08 $0
Competitor 1 New York, NY Feb-08 $0
Competitor 2 New York, NY Jan-08 $0
Competitor 2 New York, NY Feb-08 $0
Competitor 3 New York, NY Jan-08 $0
Competitor 3 New York, NY Feb-08 $0
Competitor 4 New York, NY Jan-08 $0
Competitor 4 New York, NY Feb-08 $0
Competitor 1 Los Angeles, CA Jan-08 $0
Competitor 1 Los Angeles, CA Feb-08 $4,272
Competitor 2 Los Angeles, CA Jan-08 $0
Competitor 2 Los Angeles, CA Feb-08 $0
Competitor 3 Los Angeles, CA Jan-08 $0
Competitor 3 Los Angeles, CA Feb-08 $0
Competitor 4 Los Angeles, CA Jan-08 $0
Competitor 4 Los Angeles, CA Feb-08 $0
Competitor 1 Chicago, IL Jan-08 $0
Competitor 1 Chicago, IL Feb-08 $0
Competitor 2 Chicago, IL Jan-08 $0
Competitor 2 Chicago, IL Feb-08 $0
Competitor 3 Chicago, IL Jan-08 $0
Competitor 3 Chicago, IL Feb-08 $0
Competitor 4 Chicago, IL Jan-08 $0
Competitor 4 Chicago, IL Feb-08 $0
[/tt]
Once you have your data stored in a normalized table (as above), you can just create a pivot table. The table will automatically give you subtotals by region as well as overall totals.

If you have a lot of records, you can use something like this to help you normalize your data: faq68-5287

Seriously, I cannot stress strongly enough how remarkable a tool Pivot Tables are.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Kate2008]

I am using excel 2003.
This is the formual that is giving me the error message:

=SUM(F612,F606,F600,F594,F588,F582,F576,F570,F564,F558,F552,F546,F540,F534,
F528,F522,F516,F510,F504,F498,F492,F486,F480,F474,F468,F462,F456,F450,F444,
F438,F432,F426,F420,F414,F408,F402,F396,F390,F384,F378,F372,F366,F360,F354,
F348,F342,F336,F330,F324,F318,F312,F306,F300,F294,F288,F282,F276,F270,F264,
F258,F252,F246,F240,F234,F228,F222,F216,F210,F204,F198,F192,F186,F180,F174,
F168,F162,F156,F150,F144,F138,F132,F126,F120,F114,F108,F102,F96,F90,F84,
F78,F72,F66,F60,F54,F48,F6,F12,F18,F24,F30,F36,F42)

I tried using the formula suggested: Something like:
=SumIf($A2:$A21, "Total Market Spending", D221) but that is giving me an error as well

I would chnage the format but mgmt wants things to be done in a particular way ;-/

 

[mintjulep]

That's only 102 cells, not 300.

498 characters, so you should not be up against the formula character limit.

Note that, according to help:

Arguments that are error values or text that cannot be translated into numbers cause errors.

Maybe you missed and have one or more cells in that string that are text.

 

[AnotherHiggins]

I'd steer well clear of the kind of formula you are working on. What a nightmare to keep up with! Imagine what will happen if someday there is a 5th competitor. *shiver*

I tried using the formula suggested: Something like:
=SumIf($A2:$A21, "Total Market Spending", D221) but that is giving me an error as well

Help us help you - what's the error message? (We can't read minds, you know .) Are these the proper cell references for your sheet?

I would chnage the format but mgmt wants things to be done in a particular way ;-/

I've run into those problems before, too. But don't confuse *reports* with *data storage*.

I suggest telling management that the current format is severely limiting how you can report the data. Tell them that if you change the way the data is stored, you'll still be able to provide the reports that they're used to seeing (with whatever format restrictions they want) and a whole lot more.

*IF* the data is stored properly it is a breeze to generate a report broken out annually, quarterly, etc.

They should only really care about what *reports* look like anyway, so if presented with the benefits of proper storage (and that's what we're talking about here - doing things properly), they might agree to a change. But I know things don't always work that way....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Kate,

Let me reinforce the comments offered by John regarding the difference between REPORTS & STORAGE.

You are working with a report, which complicates your ability to use Excel's features to get summary data that you require.

You would be far better to resturcture your data in tabular format, as John has suggested. From that format, your questions, and many others, can be answered in SECONDS, using one or more of Excel's data reporting, data lookup, data analysis features.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[cheerio]

Excel help defines the format of the SUM function as SUM(number1, number2, ...) where the numbers go from 1 to 30. That is probably why having 102 arguments fails.

Normally we are not limited to 30 numbers simply because each argument can be a range.

Perhaps we use SUM so often we take it for granted and do not look at help.

One more reason to accept the other advice given.