If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional sum of columns
If I have 1 row with 5 columns of data, how do I conditionally sum the
columns based on whether the column is greater than a value in another separate cell? For example, A B C D E 5 32 16 22 6 89 6 7 3 I only want to sum A5:E5 if the column is greater than the contents of A7. In this example, the answer would be (6+89)=95. If A7=1, then the answer would be (16+22+6+89)=133. |
#2
|
|||
|
|||
=sumif(A1:E1,""&A7)
"randys" wrote: If I have 1 row with 5 columns of data, how do I conditionally sum the columns based on whether the column is greater than a value in another separate cell? For example, A B C D E 5 32 16 22 6 89 6 7 3 I only want to sum A5:E5 if the column is greater than the contents of A7. In this example, the answer would be (6+89)=95. If A7=1, then the answer would be (16+22+6+89)=133. |
#3
|
|||
|
|||
Try...
=IF(A7"",SUM(INDEX(A5:E5,A7+1):E5),"") Note that the formula sums all columns when A7 equals 0. Hope this helps! In article , randys wrote: If I have 1 row with 5 columns of data, how do I conditionally sum the columns based on whether the column is greater than a value in another separate cell? For example, A B C D E 5 32 16 22 6 89 6 7 3 I only want to sum A5:E5 if the column is greater than the contents of A7. In this example, the answer would be (6+89)=95. If A7=1, then the answer would be (16+22+6+89)=133. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Importing Only Certain Columns of Text Files into Access | [email protected] | General Discussion | 2 | October 4th, 2005 08:46 PM |
Hidden Columns in Shared Workbooks | Rotary | General Discussion | 1 | July 9th, 2005 12:28 AM |
Excel columns problem | Charlie Johnson | General Discussion | 4 | October 12th, 2004 04:40 PM |
Inserted Excel Worksheet won't display all columns | Andy | General Discussion | 1 | September 30th, 2004 01:18 PM |
Average 5 columns of data skipping blank columns | marvin | Worksheet Functions | 10 | January 28th, 2004 06:33 PM |