Excellent. Worked flawlessly. Thank you!

On Apr 9, 12:24 pm, Joel wrote:
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



" wrote:
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.- Hide quoted text -

- Show quoted text -