Sum of IF Results

[Kevin Eldred]

I'm having difficulty getting a SUM of a column of IF results. The IF
statement works correctly and has a total of 8 IF's.

SUM of that column returns 0. Copy column to another and paste as values;
SUM returns 0. In empty cell, =C3+C4 returns correct value.

Any thoughts? TIA.

 

[Frank Kabel]

Hi
post your IF formula. I would assume you return strings instead of
numbers. e.g. you use something like
=IF(A1="x","1","2")
instead of
=IF(A1="x",1,2)

 

[Kevin Eldred]

Hi Frank,

You are correct. Here's the statement:

=IF(C144<6,"0",IF(C144<8,"2",IF(C144<11,"3",IF(C144<14,"4",IF(C144<17,"5",IF
(C144<20,"6",IF(C144<23,"7",IF(C144<26,"8","9"))))))))

I'm really new at this. Most of my time is CAD and GIS.

Will your last example work for me?

Thanks,

Kevin

 

[Frank Kabel]

Hi
so change it to:
=IF(C144<6,0,IF(C144<8,2,IF(C144<11,3,IF(C144<14,4,IF(C144<17,5,IF
(C144<20,6,IF(C144<23,7,IF(C144<26,8,9))))))))

Though you could shorten this to:
=IF(C144>=6,MIN(INT((C144+1)/3),9),0)

 

[Kevin Eldred]

Thank you very much. I use this to determine the number of replacement trees
based on the diameter of the tree being removed. Some of our tree lists are
in the thousands so SUM really needed to work. I just didn't have the
knowledge to write the formula correctly. I tried your shorter version but
no matter what the diameter, it returns 0.

Again, thanks for your help.

Kevin

 

[Frank Kabel]

Hi
the shorted formula works for me. What is the exact formula you have
entered?

 

[Kevin Eldred]

Frank,

I copy/pasted from your reply:

=IF(C144>=6,MIN(INT((C144+1)/3),9),0)

That's ok though, the other works wonderfully and will allow us to easily
modify if the county decides to change their environmental codes again.

Kevin