| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I use "offset" function in "array formula"?
A B C D E F G H I J
K L 1 1 2 3 4 5 6 7 8 9 10 11 12 2 10 20 30 40 50 60 70 80 90 100 110 120 My real purpose is to sum 4 cells in the second row where the cell above the first cell satisfies a condition. In other word, I want 50+60+70+80. I do not understand why the 3rd formula does not work, and why 1st and 2nd do not have the same value. I used the array formula. {=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0),""))} 50 {=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,1),""))} 10 {=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,4),""))} #N/A Thanks, Hongguang |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Threaded Mode
