Solved

# Excel dynamic sum.

Posted on 2011-04-26
273 Views
My excel file:

DATE                              1                   2                  3                  4                   5                    6
TTL Cast target             1,082        2,173        1,520        2,245        2,286        2,184

My parameter:  a
If a = 3 --> =sum(B2:D2)
if a = 2 --> = sum(B2:C2)

How can I do it?

0
Question by:JameMeck

LVL 33

Accepted Solution

If your a parameter is on cell B4, you can use a formula like this:

=IF(B4=2;SUM(B2:C2);IF(B4=3;SUM(B2:D2);""))

Please check the attached example file.

jppinto
SUM-Formula.xlsx
0

LVL 33

Expert Comment

Or do you want it to consider other values for a?
0

LVL 3

Expert Comment

=IF(condition, result if true, result if false)

so, here it would be :
=IF(a=3, sum(B2:D2), IF(a=2, sum(B2:C2), 0))

if a is neither 3 or 2 then the result is 0
0

LVL 23

Expert Comment

Where will the parameter come from

If it is in say cell a2 you could use something like

Michael

0

LVL 85

Expert Comment

=SUMIF(B1:F1,"<"&A3,B2:F2)
for example assuming your value (3 or 2 or whatever) is in A3.
0

LVL 85

Expert Comment

Typo:
=SUMIF(B1:F1,"<="&A3,B2:F2)
0

LVL 50

Expert Comment

...or you could use INDEX, something like

=SUM(B2:INDEX(B2:Z2,A3))

regards, barry
0

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.