I have a need to format cells as currency, but have to do it in-cell. This is
just like =DOLLAR() except I need it to be a currency so that it can be
summed. I have tried a lot of different steps, but cannot get the cell to
format like currency without turning it into text. I have to do this in-cell.
I cannot use format-cell-currency.

Any help is appreciated.

Thank you.

If the value is a number, so that it can be summed, it will be displayed
in accordance with the cell's display format (e.g.,
Format/Cell/Currency).

OTOH, you can coerce Text values (e.g., the result of DOLLAR()) to
numbers in your Sum function. For instance, if your Text values are in
B1:B15, array-enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(--B1:B15)


In article ,
Lyners wrote:

I have a need to format cells as currency, but have to do it in-cell. This is
just like =DOLLAR() except I need it to be a currency so that it can be
summed. I have tried a lot of different steps, but cannot get the cell to
format like currency without turning it into text. I have to do this in-cell.
I cannot use format-cell-currency.

Hi JE,
What if my cell that I summing looks like this:

=SUMIF(E1:INDIRECT(ADDRESS(ROW()-1,5)),"Revenues",F1:INDIRECT(ADDRESS(ROW()-1,6)))-SUMIF(F1:INDIRECT(ADDRESS(ROW()-1,5)),"Expenditures",F1:INDIRECT(ADDRESS(ROW()-1,6)))

Do I put "--" in front of the F1?

My Revenues and Expenditures Cells look like this:

=DOLLAR(SUMIF(D3:INDIRECT(ADDRESS(ROW()-1,4)),"",F3:INDIRECT(ADDRESS(ROW()-1,6))))

I want to sum up the values so that they look like currency. I am doing this
through a third party datagrid and that is why I have to use formulas to
format the cell.

Is there a way to do this?

Thank you

"JE McGimpsey" wrote:

If the value is a number, so that it can be summed, it will be displayed
in accordance with the cell's display format (e.g.,
Format/Cell/Currency).

OTOH, you can coerce Text values (e.g., the result of DOLLAR()) to
numbers in your Sum function. For instance, if your Text values are in
B1:B15, array-enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(--B1:B15)


In article ,
Lyners wrote:

I have a need to format cells as currency, but have to do it in-cell. This is
just like =DOLLAR() except I need it to be a currency so that it can be
summed. I have tried a lot of different steps, but cannot get the cell to
format like currency without turning it into text. I have to do this in-cell.
I cannot use format-cell-currency.