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?

JameMeckAsked:
Who is Participating?
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.