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.
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
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!
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 by kweaver; Jul 17th, 2019 at 12:29 PM.
AGGREGATE ignores errors, that might work ?
- Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
- I wear my ignorance openly, excel is not my chosen career, its a means to an ends
- Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
Code:[CODE ]Put Your Code[/ CODE]- Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
- X-Posting Guidelines Rule 13 > CHART STUFF
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!
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))
And, why doesn't =SUM(B:B) work?
sum(B:B) will total all rows in the column, and not all the values need to be counted in the sum
Hi,
SUM function Ignores non-numeric values in range:
A B C 1 1 10 2 this 3 2 4 that 5 3 6 other 7 4 Sheet690
Worksheet Formulas
Cell Formula C1 =SUM(A1:A7)
If you may have ERRORs (mixed with Text and Numbers) in range, you can use SUMIF:
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
Worksheet Formulas
Cell Formula C1 =SUMIF(A1:A10,">0",A1:A8)
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 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
Like this thread? Share it with others