Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting multiple conditions to return a figure from multiple cells | Excel Discussion (Misc queries) | |||
Search multiple cells with conditions, sum and auto populate! | Excel Worksheet Functions | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
Combining Text from multiple cells under multiple conditions | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |