SOLVED

# =sum formula with variable values

Occasional Contributor

# =sum formula with variable values

I have a data set that has five main values and each value has a different monetary payout to it. I need a to write a formula based on the five different values. For example:

Product       Amount     Payout

A                  100.00      =if A, then amount * .05

B                   100.00     =if B, then amount * .08

C                  100.00     =if C, then amount * .17

D                  100.00     =if D, then amount * .55

E                   100.00     =if E, then amount * .75

The Product column will always offer the same values. The amount column may change but the payout column will not. What's the best way to write this query?

10 Replies

# Re: =sum formula with variable values

@RazmigVarsam I suggest you make a lookup table where you list the products and payout factors. Then, use a lookup function to pick-up the correct factor and multiply that by the amount. The attached file contains two examples. One with XLOOKUP and another with VLOOKUP. See which one works for you.

# Re: =sum formula with variable values

@Riny_van_Eekelen I tried using your formula on my sheet it didn't work. Here's what my template looks like. The first box was my vlookup pivot where I have a total of five products and the two different payout percentages noted by NB and R. The second grid below that is the payout sample numbers noted in the Amount column. What am I missing here?

 Product NB R A 9.00% 9.00% H 14.00% 10.00% S 15.00% 15.00% C 12.50% 12.50% L 55.00% 5.00% Product Amount NB R A \$  1,000.00 \$      - \$- H \$  5,353.00 \$      - \$- S \$  2,562.00 \$      - \$- C \$10,000.00 \$      - \$- L \$  1,000.00 \$      - \$-

# Re: =sum formula with variable values

@RazmigVarsam Can't tell what's wrong without the file and seeing the formula used. Sorry!

# Re: =sum formula with variable values

@Riny_van_Eekelen I actually don't have a formula other than the two you suggested. I can't seem to get that formula you provided in the sample file to work for the data grid below.

 Product NB R A 5.00% 10.00% H 9.00% 5.00% S 10.00% 7.00% C 6.00% 6.00% L 25.00% 5.00% NB R A \$  1,000.00 \$      - \$- H \$  5,353.00 \$      - \$- S \$  2,562.00 \$      - \$- C \$10,000.00 \$      - \$- L \$  1,000.00 \$      - \$-

# Re: =sum formula with variable values

@RazmigVarsam Can you share your file?

# Re: =sum formula with variable values

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: =sum formula with variable values

@RazmigVarsam You didn't enter any formulas. Entered them for you. Is that what you want?

See attached.

# Re: =sum formula with variable values

That worked, thank you so much.

# Re: =sum formula with variable values

Any idea why when I drag the formula down in the cell, the formula values change?

# Re: =sum formula with variable values

nvm, I was able to figure it out. Thank you so much for your help.