Totaling cells consisting of values determined through concatenate

[brianc]

I have a worksheet with 40 rows (this number of rows will not be
static). Each row, in column "M" needs to cycle through about 10 IF
statements. To accomplish this, I used the CONCATENATE function. The
format of those cells needs to be as currency, so the CONCATENATE
function is wrapped in a TEXT format. Here is the formula below:

=TEXT(CONCATENATE(IF(J7=4.1,(L7*0.6)*140,""),IF(J7=4.2,
(L7*0.4)*140,""),IF(J7=4.22,(L7*0.4)*145,""),IF(J7=5.1,
(L7*0.6)*135,""),IF(J7=5.22,(L7*0.4)*135,""),IF(J7=5.2,
(L7*0.4)*135,""),IF(J7=8,L7*170,""),IF(J7=9,L7*120,""),IF(J7=10,130,0)),"$#,##0.00")


I now want to sum the values in those rows (all the values that need
to be summed are in column "M"). but do not seem to be able to because
of the Concatenate function. Can someone give me guidance regarding
how to get the cell values to sum together?

Thanks.

 

[omnicrondelicious]

The issue isn't the CONCATENATE I believe, so much as it is the TEXT.

In your total cell, use ="$"&SUM(VALUE(M7:M47)) and submit it with Ctrl
+Shift+Enter instead of Enter as you normally would. If done properly,
brackets {} should appear around your formula. The VALUE function
converts your text values to numeric values so they can be summed
properly. The ampersand is a convenient shorthand for CONCATENATE. The
Ctrl+Shift+Enter makes the cell an array, so that the VALUE function
can be executed for each cell before summing.

You can also use the =DOLLAR() function to get the formatting you
desire instead of hardcoding "$#,##0.00".

Hope that helps,
..o.

 

[Joel]

Make a special function like the one below. It returns a number, not text.
format the cell for currency.


call functtion as follows
=GetTotal(J7,L7)

Function GetTotal(Parm1, Parm2)

Select Case Parm1

Case 4.1
GetTotal = Parm2 * 0.6 * 140
Case 4.2
GetTotal = Parm2 * 0.4 * 140
Case 4.22
GetTotal = Parm2 * 0.4 * 145
Case 5.1
GetTotal = Parm2 * 0.6 * 135
Case 5.2
GetTotal = Parm2 * 0.4 * 135
Case 5.22
GetTotal = Parm2 * 0.4 * 135
Case 8
GetTotal = Parm2 * 170
Case 9
GetTotal = Parm2 * 120
Case 10
GetTotal = 130
Case Else
GetTotal = 0

End Select
End Function

 

[brianc]

Excellent. Worked flawlessly. Thank you!

Make a special function like the one below. It returns a number, not text.
format the cell for currency.

call functtion as follows
=GetTotal(J7,L7)

Function GetTotal(Parm1, Parm2)

Select Case Parm1

Case 4.1
GetTotal = Parm2 * 0.6 * 140
Case 4.2
GetTotal = Parm2 * 0.4 * 140
Case 4.22
GetTotal = Parm2 * 0.4 * 145
Case 5.1
GetTotal = Parm2 * 0.6 * 135
Case 5.2
GetTotal = Parm2 * 0.4 * 135
Case 5.22
GetTotal = Parm2 * 0.4 * 135
Case 8
GetTotal = Parm2 * 170
Case 9
GetTotal = Parm2 * 120
Case 10
GetTotal = 130
Case Else
GetTotal = 0

End Select
End Function






- Show quoted text -