Multiple IF conditions then add cells together
 

How do I do the following:
IF H7,J7,L7=6 is true, then add together G7,I7,K7. I want to add all
figures together that have a 6 in the adjacent column, and ignore others or
add zero.
How do I get this formula to drag across to the next column so that the '6'
changes to '7' but all else remains the same?
Also will the formula work if I have 7 columns of numbers and 7 columns of
figures?
I'm new to these conditional formulas so a specific formula would be
appreciated.
Thanks, Lil

--
Lil

Multiple IF conditions then add cells together
 

Try this:

=SUMIF($H6:$L6,COLUMNS($A1:F1),$G6:$K6)

That evaluates to:

=SUMIF($H6:$L6,6,$G6:$K6)

As you drag copy across a row it'll increment to:

=SUMIF($H6:$L6,7,$G6:$K6)
=SUMIF($H6:$L6,8,$G6:$K6)
=SUMIF($H6:$L6,9,$G6:$K6)
etc
etc

--
Biff
Microsoft Excel MVP


"Lil" wrote in message
...
How do I do the following:
IF H7,J7,L7=6 is true, then add together G7,I7,K7. I want to add all
figures together that have a 6 in the adjacent column, and ignore others
or
add zero.
How do I get this formula to drag across to the next column so that the
'6'
changes to '7' but all else remains the same?
Also will the formula work if I have 7 columns of numbers and 7 columns of
figures?
I'm new to these conditional formulas so a specific formula would be
appreciated.
Thanks, Lil

--
Lil

Multiple IF conditions then add cells together
 

How do I do the following:
IF H7,J7,L7=6 is true, then add together G7,I7,K7. I want to add all
figures together that have a 6 in the adjacent column, and ignore
others or add zero.
How do I get this formula to drag across to the next column so that
the '6' changes to '7' but all else remains the same?

One way is to put this in M7 and drag rightward.
=$G7*($H7+7=COLUMN())+
$I7*($J7+7=COLUMN())+
$K7*($L7+7=COLUMN())

It doesn't use an IF; hope that's ok.

The main idea is that the column number where the formula resides
determines the test number: 6, 7, 8, etc. M is column number 13.

Notice that if you put a logical result like ($H7+7=COLUMN()) into an
arithmetic expression, TRUE is treated as 1 (one), FALSE as 0 (zero).


Also will the formula work if I have 7 columns of numbers and 7
columns of figures?

Instead of having a sum of 3 terms, like above, expand it to 7 terms.

Multiple IF conditions then add cells together
 

Thanks, both examples work well, I just had to alter it to pick up line 7. I
don't fully understand how it picks up info in alternate columns then adds
other alternate columns, but will try to figure that one out. Thanks for the
help
--
Lil


"T. Valko" wrote:

Try this:

=SUMIF($H6:$L6,COLUMNS($A1:F1),$G6:$K6)

That evaluates to:

=SUMIF($H6:$L6,6,$G6:$K6)

As you drag copy across a row it'll increment to:

=SUMIF($H6:$L6,7,$G6:$K6)
=SUMIF($H6:$L6,8,$G6:$K6)
=SUMIF($H6:$L6,9,$G6:$K6)
etc
etc

--
Biff
Microsoft Excel MVP


"Lil" wrote in message
...
How do I do the following:
IF H7,J7,L7=6 is true, then add together G7,I7,K7. I want to add all
figures together that have a 6 in the adjacent column, and ignore others
or
add zero.
How do I get this formula to drag across to the next column so that the
'6'
changes to '7' but all else remains the same?
Also will the formula work if I have 7 columns of numbers and 7 columns of
figures?
I'm new to these conditional formulas so a specific formula would be
appreciated.
Thanks, Lil

--
Lil

Multiple IF conditions then add cells together
 

Thanks for replying, I had trouble getting this to work, but another formula
given to me seems to be working well. Thanks for your reply
--
Lil


"MyVeryOwnSelf" wrote:

How do I do the following:
IF H7,J7,L7=6 is true, then add together G7,I7,K7. I want to add all
figures together that have a 6 in the adjacent column, and ignore
others or add zero.
How do I get this formula to drag across to the next column so that
the '6' changes to '7' but all else remains the same?

One way is to put this in M7 and drag rightward.
=$G7*($H7+7=COLUMN())+
$I7*($J7+7=COLUMN())+
$K7*($L7+7=COLUMN())

It doesn't use an IF; hope that's ok.

The main idea is that the column number where the formula resides
determines the test number: 6, 7, 8, etc. M is column number 13.

Notice that if you put a logical result like ($H7+7=COLUMN()) into an
arithmetic expression, TRUE is treated as 1 (one), FALSE as 0 (zero).


Also will the formula work if I have 7 columns of numbers and 7
columns of figures?

Instead of having a sum of 3 terms, like above, expand it to 7 terms.

Multiple IF conditions then add cells together
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lil" wrote in message
...
Thanks, both examples work well, I just had to alter it to pick up line 7.
I
don't fully understand how it picks up info in alternate columns then adds
other alternate columns, but will try to figure that one out. Thanks for
the
help
--
Lil


"T. Valko" wrote:

Try this:

=SUMIF($H6:$L6,COLUMNS($A1:F1),$G6:$K6)

That evaluates to:

=SUMIF($H6:$L6,6,$G6:$K6)

As you drag copy across a row it'll increment to:

=SUMIF($H6:$L6,7,$G6:$K6)
=SUMIF($H6:$L6,8,$G6:$K6)
=SUMIF($H6:$L6,9,$G6:$K6)
etc
etc

--
Biff
Microsoft Excel MVP


"Lil" wrote in message
...
How do I do the following:
IF H7,J7,L7=6 is true, then add together G7,I7,K7. I want to add all
figures together that have a 6 in the adjacent column, and ignore
others
or
add zero.
How do I get this formula to drag across to the next column so that the
'6'
changes to '7' but all else remains the same?
Also will the formula work if I have 7 columns of numbers and 7 columns
of
figures?
I'm new to these conditional formulas so a specific formula would be
appreciated.
Thanks, Lil

--
Lil