SUM ignoring text fields in a non-string of cells

[JSC318]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Person[/TD]
[TD]Day 1[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]User 1 [/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A10[/TD]
[TD]User 2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]A16[/TD]
[TD]User 3[/TD]
[TD]Sick[/TD]
[/TR]
[TR]
[TD]A84[/TD]
[TD]User 4[/TD]
[TD]Pto[/TD]
[/TR]
[TR]
[TD]A101[/TD]
[TD]User 5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A122[/TD]
[TD]User 6[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[kweaver]

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.

 

[mole999]

AGGREGATE ignores errors, that might work ?

 

[JSC318]

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!

 

[JSC318]

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))

 

[kweaver]

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

 

[JSC318]

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

 

[jtakw]

Hi,

SUM function Ignores non-numeric values in range:

Book1
	A	B	C
1	1		10
2	this
3	2
4	that
5	3
6	other
7	4
Sheet690

Cell Formulas
Range	Formula
C1	=SUM(A1:A7)

 

[jtakw]

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

Book1
	A	B	C
1	1		15
2	this
3	2
4	#N/A
5	3
6	that
7	#DIV/0!
8	4
9	#VALUE!
10	5
Sheet690

Cell Formulas
Range	Formula
C1	=SUMIF(A1:A10,">0",A1:A8)

 

[JSC318]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12
[/TD]
[TD]13[/TD]
[TD]sick
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]sick[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]pto[/TD]
[TD]54[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7[/TD]
[TD]pto[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]pto
[/TD]
[TD]10[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]sick[/TD]
[TD]9[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]pto[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]77[/TD]
[TD]sick[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]pto[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]sick[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SUM of Red in Column A[/TD]
[TD]SUM of Red in Column B[/TD]
[TD]SUM of Red in Column C[/TD]
[/TR]
</tbody>[/TABLE]