Software

Ask something
Discussion Locked

Excel formula or help

0 helpful
7 replies

by whatme?

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a list box in A43. they choose "NU" "NT"or "CA" at the beginning of each row
an ex of one row:
In cells h43:k43, the user will place an "x".
Only those boxes (h43:k43) with an x are totalled.
If they choose A43 (NU), then the values in h43=20,i43=25,j43=30,k43=40. The total will be in L43
If they choose A43(NT), then the values change in h43=12,i43=17,j43=22,k43=28
If they choose A43(CA), then the values change in h43=10,i43=14,j43=17,k43=20

Thanks for any help

7 replies

by whatme?

Sorry.. the question is:
What formula would I use in cell L43 to get the total.
If A43=NU..then add the total of h43:k43 if there is an x in one of those boxes... see the values above

by whatme?

I just created tables for each region (NU,NT,CA)
in columns M, N, O, P, then in Q,R,S,T are totals if an "x" is placed in cells h43:k43

the formula which is probably quite wrong is:
=IF(B43="NU",(Q20:T30),"NT",(Q10:T10),"CA",(Q30:T30))

by firstpeter

If you can use another row for calculations, I think you'll find it to be MUCH less painful. Otherwise I think you'll end up with a gigantic IF statement that runs through each possibility.

If you can use row 44:

In cells H44:K44 plug in IF statements that determine the appropriate value if the "x" were selected (i.e., for NU it would be 20, 25, 30, and 40 in columns H, I, J, and K). You can then plug in an array in L43 to get the answer:

{=SUM(IF(H43:K43="x",H44:K44))}

On the flip side, instead of using row 44 to just plug in the values based on cell A43 (i.e., 20, 25, 30, and 40), you could have it plug in zero if the cell doesn't have an "x", or the appropriate number if it does.

by whatme?

Well, I worked through it. Couldn't figure out the array bit but I did use separate cells for my different tables(NU,NT, CA)then summed them up if there is an "x" in the range H43:k43and this is the formula that now works
=SUM(IF(B43="NU",CI43,(IF(B43="NT",CI9,(IF(B43="CA",CI68,0))))))

I am sure there is one formula to do it all but this is a great start.

Thanks
mtulloch@nulas.ca

This discussion is closed to new replies.