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:

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

<tbody>
</tbody>

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.

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

<tbody>
</tbody>

 

[RoryA]

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