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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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....
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 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.

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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top