• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Excel dynamic sum.

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
JameMeck
Asked:
JameMeck
1 Solution
 
jppintoCommented:
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
 
jppintoCommented:
Or do you want it to consider other values for a?
0
 
Statick001Commented:
=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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Michael FowlerSolutions ConsultantCommented:
Where will the parameter come from

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

=SUM(INDIRECT(ADDRESS(1,2)):INDIRECT(ADDRESS(1,2+ A2)))

Michael

0
 
Rory ArchibaldCommented:
=SUMIF(B1:F1,"<"&A3,B2:F2)
for example assuming your value (3 or 2 or whatever) is in A3.
0
 
Rory ArchibaldCommented:
Typo:
=SUMIF(B1:F1,"<="&A3,B2:F2)
0
 
barry houdiniCommented:
...or you could use INDEX, something like

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

regards, barry
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now