Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #466 www.energyefficientbuild.com
user BwC13 said: Want to add an array based on If function This is what I am doing. I have prioritized cells in column C with a corresponding 1,2,3,4,or 5 in column b. I want to add all the cells in column c that have a corresponding 1 in column b. I know I can do this: if(b2=1, c2)+if(b3=1, c3) But I have a ton of cells to add up so I hope someone knows a shortcut. In addition, I also want to include 3 more "add" columns and their corresponding "1,2,3,4,or 5" column to the equation. I can probably figure this part out on my own if someone can do the first part for me. thanks
UPDATE:
user BwC13 said:
Found a solution: =sumif(B2:B17,"1",C2:C17)+sumif(E2:E17,"1",F2:F17)+sumif(H2:H17,"1",I2:I17)+sumif(K2:K17,"1",L2:L17) Thanks for looking
---------------------------------------------------
I noticed that there is a pattern to user BwC13's data ... so if there would be many more sets of columns to be considered and perhaps some other pattern, it would be nice to have a generalized solution ... and it is for that problem that I have presented a solution in the following:
user BwC13 said: Want to add an array based on If function This is what I am doing. I have prioritized cells in column C with a corresponding 1,2,3,4,or 5 in column b. I want to add all the cells in column c that have a corresponding 1 in column b. I know I can do this: if(b2=1, c2)+if(b3=1, c3) But I have a ton of cells to add up so I hope someone knows a shortcut. In addition, I also want to include 3 more "add" columns and their corresponding "1,2,3,4,or 5" column to the equation. I can probably figure this part out on my own if someone can do the first part for me. thanks
UPDATE:
user BwC13 said:
Found a solution: =sumif(B2:B17,"1",C2:C17)+sumif(E2:E17,"1",F2:F17)+sumif(H2:H17,"1",I2:I17)+sumif(K2:K17,"1",L2:L17) Thanks for looking
---------------------------------------------------
I noticed that there is a pattern to user BwC13's data ... so if there would be many more sets of columns to be considered and perhaps some other pattern, it would be nice to have a generalized solution ... and it is for that problem that I have presented a solution in the following:
No comments:
Post a Comment