Problems with copying functions
I used an "IF" function to make a column Q of "y" and "n" entries, as such:
=IF(P30,"Y","N") I then used the following function: =IF(Lines!$Q$3:$Q$57="n",SUMIF(Lines!$I$3:$I$57,"= 0",Lines!$F$3:$F$57),0) i repeated the function for "y": =IF(Lines!$Q$3:$Q$57="y",SUMIF(Lines!$I$3:$I$57,"= 0",Lines!$F$3:$F$57),0) For some reason it will calculate the first formula, but not the second when i paste it to another column. i don't understand why it works for one and not the other...any ideas? |
Problems with copying functions
Hi,
It;s an array so when you copy the formula commit it by pressing Ctrl+Shift+Enter Mike "martinbarnes" wrote: I used an "IF" function to make a column Q of "y" and "n" entries, as such: =IF(P30,"Y","N") I then used the following function: =IF(Lines!$Q$3:$Q$57="n",SUMIF(Lines!$I$3:$I$57,"= 0",Lines!$F$3:$F$57),0) i repeated the function for "y": =IF(Lines!$Q$3:$Q$57="y",SUMIF(Lines!$I$3:$I$57,"= 0",Lines!$F$3:$F$57),0) For some reason it will calculate the first formula, but not the second when i paste it to another column. i don't understand why it works for one and not the other...any ideas? |
Problems with copying functions
Check your earlier post.
martinbarnes wrote: I used an "IF" function to make a column Q of "y" and "n" entries, as such: =IF(P30,"Y","N") I then used the following function: =IF(Lines!$Q$3:$Q$57="n",SUMIF(Lines!$I$3:$I$57,"= 0",Lines!$F$3:$F$57),0) i repeated the function for "y": =IF(Lines!$Q$3:$Q$57="y",SUMIF(Lines!$I$3:$I$57,"= 0",Lines!$F$3:$F$57),0) For some reason it will calculate the first formula, but not the second when i paste it to another column. i don't understand why it works for one and not the other...any ideas? -- Dave Peterson |
Problems with copying functions
Do these SUMPRODUCT formulas give the results you are looking for?
=SUMPRODUCT((Lines!$Q$3:$Q$57="n")*(Lines!$I$3:$I$ 57=0)*(Lines!$F$3:$F$57)) =SUMPRODUCT((Lines!$Q$3:$Q$57="y")*(Lines!$I$3:$I$ 57=0)*(Lines!$F$3:$F$57)) Hope this helps, Hutch "martinbarnes" wrote: I used an "IF" function to make a column Q of "y" and "n" entries, as such: =IF(P30,"Y","N") I then used the following function: =IF(Lines!$Q$3:$Q$57="n",SUMIF(Lines!$I$3:$I$57,"= 0",Lines!$F$3:$F$57),0) i repeated the function for "y": =IF(Lines!$Q$3:$Q$57="y",SUMIF(Lines!$I$3:$I$57,"= 0",Lines!$F$3:$F$57),0) For some reason it will calculate the first formula, but not the second when i paste it to another column. i don't understand why it works for one and not the other...any ideas? |
All times are GMT +1. The time now is 10:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com