Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Conditional Sum
Using the same formula, I want to be able to sum 3 different sets of data
independently based on a pointer in a cell. For example: A B C D E F G H I 1 2 10 20 30 3 5 10 15 4 First 2 4 6 5 Using a formula in cell A5, based on the information in cell A4, sum the appropriate row. In cell A4, First = row 2, Second = row 3, Third = row 4. The above answer in cell A5 would be 10+20+30=60. If I changed A4 to "Second", then the answer in cell A5 is 30. Can this be done? |
#2
|
|||
|
|||
Assuming that D2:F4 contains your data, try...
=SUM(INDEX(D2:F4,MATCH(A4,{"First","Second","Third "},0),0)) Hope this helps! In article , randys wrote: Using the same formula, I want to be able to sum 3 different sets of data independently based on a pointer in a cell. For example: A B C D E F G H I 1 2 10 20 30 3 5 10 15 4 First 2 4 6 5 Using a formula in cell A5, based on the information in cell A4, sum the appropriate row. In cell A4, First = row 2, Second = row 3, Third = row 4. The above answer in cell A5 would be 10+20+30=60. If I changed A4 to "Second", then the answer in cell A5 is 30. Can this be done? |
#3
|
|||
|
|||
THANK YOU for the quick answer. Let me complicate it a bit more ... (not that
I want to, but this is closer to the problem I'm trying to solve) Using the same formula, I want to be able to sum 3 different sets of data independently based on a pointer in a cell. For example: A B C D E F G 1 First <formula 2 10 Second 3 5 4 2 5 6 Second 7 20 8 10 9 4 10 .. .. .. 21 Third 22 30 23 15 24 6 Using a formula in cell G1, based on the information in cell G2, sum the appropriate 3 values in column D. If G2="First", the answer would be 10+5+2=17. If G2="Second", the answer would be 20+10+4=34. If G2="Third", the answer would be 30+15+6=51. Please help! "Domenic" wrote: Assuming that D2:F4 contains your data, try... =SUM(INDEX(D2:F4,MATCH(A4,{"First","Second","Third "},0),0)) Hope this helps! In article , randys wrote: Using the same formula, I want to be able to sum 3 different sets of data independently based on a pointer in a cell. For example: A B C D E F G H I 1 2 10 20 30 3 5 10 15 4 First 2 4 6 5 Using a formula in cell A5, based on the information in cell A4, sum the appropriate row. In cell A4, First = row 2, Second = row 3, Third = row 4. The above answer in cell A5 would be 10+20+30=60. If I changed A4 to "Second", then the answer in cell A5 is 30. Can this be done? |
#4
|
|||
|
|||
Assuming that each set contains three rows, try...
=SUM(OFFSET(D1,MATCH(G2,A:A,0),0,3)) Hope this helps! In article , randys wrote: THANK YOU for the quick answer. Let me complicate it a bit more ... (not that I want to, but this is closer to the problem I'm trying to solve) Using the same formula, I want to be able to sum 3 different sets of data independently based on a pointer in a cell. For example: A B C D E F G 1 First <formula 2 10 Second 3 5 4 2 5 6 Second 7 20 8 10 9 4 10 . . . 21 Third 22 30 23 15 24 6 Using a formula in cell G1, based on the information in cell G2, sum the appropriate 3 values in column D. If G2="First", the answer would be 10+5+2=17. If G2="Second", the answer would be 20+10+4=34. If G2="Third", the answer would be 30+15+6=51. Please help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
conditional formatting conflict? | Excel Worksheet Functions | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |