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!

 

[JSC318]

Update! I have a solve!

Aggregate replacing the + with ,



=AGGREGATE(9,7,(D6,D15,D24,D33,D42,D51,D60,D69,D78,D87,D96,D105,D114,D123,D132,D141,D150,D159,D168,D177,D186))

 

[RoryA]

OK, so why couldn't you just use:

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

 

[JSC318]

Because some cells contained text, so was returning #value result.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[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]

 

[RoryA]

SUM ignores text and would not give you a #VALUE ! result with that data.