Maximum range for sum() function?

[markjr]

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.

 

[barry houdini]

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]

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

 

[Aladin Akyurek]

What do you get with:

=SUMPRODUCT(A1:A44582+0)

 

[markjr]

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]

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]

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

 

[Aladin Akyurek]

Care to post 5 rows of data along with the required results?

 

[markjr]

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]

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?