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?

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?

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

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?