Share via

The sum function does not recognize the number data in Excel 2010 and will not work.

Anonymous
2016-07-08T14:36:01+00:00

The number value for each cell is coming from an IF formula:

=IF(C2="Short Sleeve","$10",IF(C2="Long Sleeve","$15",IF(C2="Sweatshirt","$25","0")))

So in each cell in the column, there is a currency value listed of $10, $15, or $25.  These cells are formatted as a currency as well.  I've also tried removing the $ sign in the IF formula and formatting as a number but neither work.  So I assume that Excel recognizes the "$10", "$15", or "$25" as text which is why the Sum function won't work.  However, when I add each cell individually (i.e. A1+B1+C1), it works fine.  Here are some pictures:

IF Formula:

SUM Formula does not work:

Arithmetic does work:

Any thoughts?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2016-07-08T14:44:52+00:00

    Excel sees values such as "$25" as text values, not as numbers.

    Change the formula in D2 to

    =IF(C2="Short Sleeve",10,IF(C2="Long Sleeve",15,IF(C2="Sweatshirt",25,0)))

    and fill down.

    Select D2:D12 and set the number format to Currency. This will make 10 display as $10.00 etc.

    3 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-07-08T14:49:55+00:00

    Thank you!  That worked perfectly.

    0 comments