# Thread: SUM ignoring text fields in a non-string of cells Thanks: 0 Likes: 0

1. ## SUM ignoring text fields in a non-string of cells

Hello,

I am looking to add cell values together, i keep having the value column, an e.g. of what i am trying to add below:

 Cell Person Day 1 A1 User 1 10 A10 User 2 0 A16 User 3 Sick A84 User 4 Pto A101 User 5 9 A122 User 6 12

I am trying to add the amount sold on each day (in the above e.g. Day 1), I want the formula to ignore cells with the word Sick or PTO, i can't use SUM or "<>#N/A" because the cells i am adding up are random and not in a grouped together range, I need to be able to drag the formula across as it will eventually cover a full year.

Thank you!

2. ## Re: SUM ignoring text fields in a non-string of cells

Are you trying to find the sum of the Day1 column for all users or for each one?
Since SUM ignores text, why doesn't =SUM(B:B) work if the DAY column is column B?

If this is not it, you need to explain what you're looking for.

3. ## Re: SUM ignoring text fields in a non-string of cells

AGGREGATE ignores errors, that might work ?

4. ## Re: SUM ignoring text fields in a non-string of cells

Thank you - I am trying to find the sum of Day 1, however not all the cells in this column (see e.g. see Cell column in my example) - I can't get A:A to or A1:A100 to work as it's not all the cells i need to add up!

5. ## Re: SUM ignoring text fields in a non-string of cells

I;ve tried Aggregate vut still getting the #Value =AGGREGATE(9,6,(D6+D15+D24+D33+D42+D51+D60+D69+D78+D87+D96+D105+D114+D123+D132+D141+D150+D159+D168+D177+D186))

6. ## Re: SUM ignoring text fields in a non-string of cells

And, why doesn't =SUM(B:B) work?

7. ## Re: SUM ignoring text fields in a non-string of cells

sum(B:B) will total all rows in the column, and not all the values need to be counted in the sum

8. ## Re: SUM ignoring text fields in a non-string of cells

Hi,

SUM function Ignores non-numeric values in range:

ABC
1110
2this
32
4that
53
6other
74

Sheet690

Worksheet Formulas
CellFormula
C1=SUM(A1:A7)

9. ## Re: SUM ignoring text fields in a non-string of cells

If you may have ERRORs (mixed with Text and Numbers) in range, you can use SUMIF:

ABC
1115
2this
32
4#N/A
53
6that
7#DIV/0!
84
9#VALUE!
105

Sheet690

Worksheet Formulas
CellFormula
C1=SUMIF(A1:A10,">0",A1:A8)

10. ## Re: SUM ignoring text fields in a non-string of cells

Thank you, i've tried that but it's still not working:

=sumif(D6+D15+D24+D33+D42+D51+D60+D69+D78+D87+D96+D105+D114+D123+D132+D141+D150+D159+D168+D177+D186,">0",D6+D15+D24+D33+D42+D51+D60+D69+D78+D87+D96+D105+D114+D123+D132+D141+D150+D159+D168+D177+D186)

Think maybe i haven't been super clear, I don't want to sum all the numbers in that column just some of them (see e.g. below)

Thank you

 Column A Column B Column C 12 13 sick 5 sick 11 pto 54 9 7 pto 5 6 6 3 pto 10 13 sick 9 6 3 5 8 5 4 4 pto 6 4 77 sick 7 8 9 pto 9 3 4 0 sick 4 4 3 4 SUM of Red in Column A SUM of Red in Column B SUM of Red in Column C