Totaling cells consisting of values determined through concatenate

B

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.
 
O

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.
 
J

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
 
B

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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top