SUM ignoring text fields in a non-string of cells

JSC318

New Member
Joined
Jul 17, 2019
Messages
7
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:

CellPersonDay 1
A1User 1 10
A10User 20
A16User 3Sick
A84User 4Pto
A101User 59
A122User 612

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

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Last edited:
Upvote 0
AGGREGATE ignores errors, that might work ?
 
Upvote 0
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!
 
Upvote 0
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))
 
Upvote 0
sum(B:B) will total all rows in the column, and not all the values need to be counted in the sum :)
 
Upvote 0
Hi,

SUM function Ignores non-numeric values in range:


Book1
ABC
1110
2this
32
4that
53
6other
74
Sheet690
Cell Formulas
RangeFormula
C1=SUM(A1:A7)
 
Upvote 0
If you may have ERRORs (mixed with Text and Numbers) in range, you can use SUMIF:


Book1
ABC
1115
2this
32
4#N/A
53
6that
7#DIV/0!
84
9#VALUE!
105
Sheet690
Cell Formulas
RangeFormula
C1=SUMIF(A1:A10,">0",A1:A8)
 
Upvote 0
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 AColumn BColumn C
12
13sick
5sick11
pto549
7pto5
663
pto
1013
sick96

358
544
pto64
77sick7
89pto
934
0sick4
434
SUM of Red in Column ASUM of Red in Column BSUM of Red in Column C

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top