Maximum range for sum() function?

markjr

New Member
Joined
Aug 25, 2009
Messages
5
I am trying to sum 44,582 cells in a column, the values in all of them are either 0 or 1, but I always get a value of 0.

It works when I bring this right down to 1000 or 1500 cells but it can't seem to sum the entire range.

This is Excel 2004 for Mac OSX Version 11.3.5

Any help appreciated, this is driving me nuts.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Helo markjr, welcome to MrExcel,

I presume you are using a simple SUM function, e.g.

=SUM(A1:A44582)

How are the 1s and zeroes generated? If the 1s are text, rather than numeric, then you might get zero. You could try counting 1s instead

=COUNTIF(A1:A44582,1)

that won't distinguish between text and number....
 

markjr

New Member
Joined
Aug 25, 2009
Messages
5
Helo markjr, welcome to MrExcel,

I presume you are using a simple SUM function, e.g.

=SUM(A1:A44582)

How are the 1s and zeroes generated? If the 1s are text, rather than numeric, then you might get zero. You could try counting 1s instead

=COUNTIF(A1:A44582,1)

that won't distinguish between text and number....

Thanks for the reply.

=COUNTIF gets me the same 0 result.

The column is formatted as a numeric column.

The 1's or 0's are generated by forumulas like this:

=IF(C9<$J$4,1,0)

Where that C9 ranges from 4 to 44582
 

markjr

New Member
Joined
Aug 25, 2009
Messages
5

ADVERTISEMENT

What do you get with:

=SUMPRODUCT(A1:A44582+0)


Waitaminute.

I just stepped away from my computer for a few minutes and when I came back, there was a value there in the cell, in fact all the cells I was trying to sum now have values (even though the rest of them are still using sum())

Could the spreadsheet just have been taking a loooong time to recalculate?

Anyway, thanks for the help, this board is a fantastic resource.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Waitaminute.

I just stepped away from my computer for a few minutes and when I came back, there was a value there in the cell, in fact all the cells I was trying to sum now have values (even though the rest of them are still using sum())

Could the spreadsheet just have been taking a loooong time to recalculate?

Anyway, thanks for the help, this board is a fantastic resource.

Such a SUM formula shouldn't take much time. Maybe you have too many formulas with OFFSET, INDIRECT, or other volatile functions in you workbook.
 

markjr

New Member
Joined
Aug 25, 2009
Messages
5

ADVERTISEMENT

Such a SUM formula shouldn't take much time. Maybe you have too many formulas with OFFSET, INDIRECT, or other volatile functions in you workbook.

I have two columns

textfield, counter

there are 44,582 rows

then I have four columns for each one:

=IF(C4<$J$4,1,0)
=IF(D4=0,IF(C4<$J$5,1,0),0)
=IF(SUM($D4:E4)=0,IF(C4<$J$6,1,0),0)
=IF(SUM($D4:F4)=0,IF(C4>$J$6,1,0),0)

Basically what I am trying to do is slot each row into one of four categories based on thresholds, 1000000, 10000000, 100000000 and above 100000000

The nested if's may not be helping, but I find compound if's don't work:

if(sum($D4:f4)=0&C4>$J$6,1,0)) always evals to 0, even though each side of the compound statement works on it's own. (Am I missing something here)

I didn't know about countif, so now I'm trying:

=COUNTIF(C4:C44582,"<$j$4")

for my first column

but again, that is coming out to 0
 

markjr

New Member
Joined
Aug 25, 2009
Messages
5
Care to post 5 rows of data along with the required results?


Sure:

Thresholds:
Categories widgets/mo
Level 1 1,000,000
Level 2 10,000,000
Level 3 100,000,000
Level 4 (anything above Level 3)


Data:

Name Widgets Level1 Level2 Level3 Level4

Foobar 200,000,000 0 0 0 1
Barfoo 90,000,000 0 0 1 0
Wahoo 8,000,000 0 1 0 0
Hoowa 500,000 1 0 0 0

Then I sum each Level1, Level2, Level3, Level4

Ideally, I just want to get to here:

Level1: 20 members
Level2: 12 members
Level3: 220 members
Level4: 1000 members

Where I'm counting the members based on the widget count.

The only way I can figure out how to do it in a spreadsheet is to setup that four column matrix where I set a bit for each entry and then count up those bits.

The other thing is I need to be able to change the thresholds, countif() looked promising until I realized I had to hardcode the value in criterea, I can't have the criterea reference a cell from the looks of it.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Sure:

Thresholds:
Categories widgets/mo
Level 1 1,000,000
Level 2 10,000,000
Level 3 100,000,000
Level 4 (anything above Level 3)


Data:

Name Widgets Level1 Level2 Level3 Level4

Foobar 200,000,000 0 0 0 1
Barfoo 90,000,000 0 0 1 0
Wahoo 8,000,000 0 1 0 0
Hoowa 500,000 1 0 0 0

Then I sum each Level1, Level2, Level3, Level4

Ideally, I just want to get to here:

Level1: 20 members
Level2: 12 members
Level3: 220 members
Level4: 1000 members

Where I'm counting the members based on the widget count.

The only way I can figure out how to do it in a spreadsheet is to setup that four column matrix where I set a bit for each entry and then count up those bits.

The other thing is I need to be able to change the thresholds, countif() looked promising until I realized I had to hardcode the value in criterea, I can't have the criterea reference a cell from the looks of it.

Still unclear (to me at least). How do you get from Data to the result Level 1: 20 members?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,131
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top