Multiple IF conditions then add cells together

L

Lil

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
 
T

T. Valko

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
 
M

MyVeryOwnSelf

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.
 
L

Lil

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
 
L

Lil

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top