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!
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,068
Office Version
365, 2010
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:

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,359
Office Version
2019, 2016, 2013
Platform
Windows
AGGREGATE ignores errors, that might work ?
 

JSC318

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

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

Well-known Member
Joined
May 8, 2018
Messages
1,068
Office Version
365, 2010
And, why doesn't =SUM(B:B) work?
 

JSC318

New Member
Joined
Jul 17, 2019
Messages
7
sum(B:B) will total all rows in the column, and not all the values need to be counted in the sum :)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

SUM function Ignores non-numeric values in range:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">this</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">that</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">other</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet690</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=SUM(<font color="Blue">A1:A7</font>)</td></tr></tbody></table></td></tr></table><br />
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
If you may have ERRORs (mixed with Text and Numbers) in range, you can use SUMIF:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">this</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">that</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">#DIV/0!</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">#VALUE!</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet690</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=SUMIF(<font color="Blue">A1:A10,">0",A1:A8</font>)</td></tr></tbody></table></td></tr></table><br />
 

JSC318

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

Watch MrExcel Video

Forum statistics

Threads
1,101,815
Messages
5,483,075
Members
407,377
Latest member
JennaWashburn

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top