sumif?
 

Ar Bs Ec Ar Bs Ec
20 36 14 10 7

I am trying to sum the above columns. Column 1-3 supercede columns 4-6, in
the sense that if column 4 has a figure in there and so does column 1, it is
column 1s figure that needs to be included in the total not column 4. So for
the above example i would need the formula to work out 20+36+7=63.

How would i do this?

sumif?
 

Correct, you can use SUMIF.

=SUM(A2:C2,SUMIF(A2:C2,"=",D2:F2))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mits" wrote:

Ar Bs Ec Ar Bs Ec
20 36 14 10 7

I am trying to sum the above columns. Column 1-3 supercede columns 4-6, in
the sense that if column 4 has a figure in there and so does column 1, it is
column 1s figure that needs to be included in the total not column 4. So for
the above example i would need the formula to work out 20+36+7=63.

How would i do this?

sumif?
 

Hi,
in a hidden row you can enter a helper, let's say row 1 so for columns 1 to
three enter

=IF(A2<"",1,0)
copy this up to column 3 and then in column 4 enter

=IF(A20,0,1)

copy to column 6



then in column G2 enter

=SUMPRODUCT(--(A1:F1=1),A2:F2)

if this helps please click yes



"mits" wrote:

Ar Bs Ec Ar Bs Ec
20 36 14 10 7

I am trying to sum the above columns. Column 1-3 supercede columns 4-6, in
the sense that if column 4 has a figure in there and so does column 1, it is
column 1s figure that needs to be included in the total not column 4. So for
the above example i would need the formula to work out 20+36+7=63.

How would i do this?

sumif?
 

thanks for your reply. Sorry but this is giving me a 0 figure.

"Luke M" wrote:

Correct, you can use SUMIF.

=SUM(A2:C2,SUMIF(A2:C2,"=",D2:F2))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mits" wrote:

Ar Bs Ec Ar Bs Ec
20 36 14 10 7

I am trying to sum the above columns. Column 1-3 supercede columns 4-6, in
the sense that if column 4 has a figure in there and so does column 1, it is
column 1s figure that needs to be included in the total not column 4. So for
the above example i would need the formula to work out 20+36+7=63.

How would i do this?

sumif?
 

thanks, but i wanted to sum the actual cells to get a total at the end if
thats possible

"Eduardo" wrote:

Hi,
in a hidden row you can enter a helper, let's say row 1 so for columns 1 to
three enter

=IF(A2<"",1,0)
copy this up to column 3 and then in column 4 enter

=IF(A20,0,1)

copy to column 6



then in column G2 enter

=SUMPRODUCT(--(A1:F1=1),A2:F2)

if this helps please click yes



"mits" wrote:

Ar Bs Ec Ar Bs Ec
20 36 14 10 7

I am trying to sum the above columns. Column 1-3 supercede columns 4-6, in
the sense that if column 4 has a figure in there and so does column 1, it is
column 1s figure that needs to be included in the total not column 4. So for
the above example i would need the formula to work out 20+36+7=63.

How would i do this?

sumif?
 

Hi,
you are summing the actuAL cells but using your helper, the sum formula is
as posted

=SUMPRODUCT(--(A1:F1=1),A2:F2)

"mits" wrote:

thanks, but i wanted to sum the actual cells to get a total at the end if
thats possible

"Eduardo" wrote:

Hi,
in a hidden row you can enter a helper, let's say row 1 so for columns 1 to
three enter

=IF(A2<"",1,0)
copy this up to column 3 and then in column 4 enter

=IF(A20,0,1)

copy to column 6



then in column G2 enter

=SUMPRODUCT(--(A1:F1=1),A2:F2)

if this helps please click yes



"mits" wrote:

Ar Bs Ec Ar Bs Ec
20 36 14 10 7

I am trying to sum the above columns. Column 1-3 supercede columns 4-6, in
the sense that if column 4 has a figure in there and so does column 1, it is
column 1s figure that needs to be included in the total not column 4. So for
the above example i would need the formula to work out 20+36+7=63.

How would i do this?

sumif?
 

In case you have formulas generating blank cells:

=SUM(A2:C2,SUMPRODUCT((A2:C2="")*(D2:F2)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mits" wrote:

thanks for your reply. Sorry but this is giving me a 0 figure.

"Luke M" wrote:

Correct, you can use SUMIF.

=SUM(A2:C2,SUMIF(A2:C2,"=",D2:F2))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mits" wrote:

Ar Bs Ec Ar Bs Ec
20 36 14 10 7

I am trying to sum the above columns. Column 1-3 supercede columns 4-6, in
the sense that if column 4 has a figure in there and so does column 1, it is
column 1s figure that needs to be included in the total not column 4. So for
the above example i would need the formula to work out 20+36+7=63.

How would i do this?