How can I sum totals in Excel if the identifying mark is an asterisk *. In column D I have numerous accounts only identified with an asterisk * in column E I have totals. I need to write a formula that will sum only the accounts labeled as an *. Thanks
1
-
Can you give a short example screenshot of what your data looks like?– Michael FrankOct 5, 2015 at 21:09
Add a comment
|
3 Answers
Try putting the following formula into all the cells in a new column
=IF(D1="*", E1, 0)
Then sum that column at the end of it
-
After reading your answer, I realise mine doesn't even answer the question. Nice work. Oct 5, 2015 at 21:08
-
@Susan No problem. Don't forget to check the check mark by the answer to mark it as accepted so I get credit. Thanks– sa289Oct 5, 2015 at 23:32
You can use the SUMIF function to achieve that in only one step.
SUMIF(range, criteria, [sum_range])
Just use the following formula in a new cell:
=SUMIF(D1:D99,"=~*",E1:E99)
answered Oct 5, 2015 at 21:13
-
Is the "~" so that "*" gets treated as a literal rather than a wildcard? If so, might that vary by locale setting (need "\" in some locales)? I'm using LO Calc and don't have ready access to Excel to test it, but in Calc, a plain asterisk works. Oct 6, 2015 at 0:56
-
@fixer1234 In Excel, ~? finds only the ? character; ~* finds only the * character; ? finds all single characters; * finds all strings of characters. I don't think it varies by locale settings. Oct 7, 2015 at 18:38
-
No problem with the "?" or "*". I was referring to "~". My understanding is that "\" is typically used to treat the next character as a literal, so I was wondering if that difference ("~" vs. "\"), is locale-based. Oct 7, 2015 at 18:52
You can use COUNTIF for this.
This will count all the cells with * between A1 and A15:
=COUNTIF(A1:A15,"*")
answered Oct 5, 2015 at 21:06
