My spread sheet contains already some COUNTIF formula which are working
okay.

As soon as i try to change which cells are included in the formula so
they do not go in sequence i get the #VALUE! coming up

the one that works goes as follows

=SUM(AA200:AA205)/COUNTIF(AA200:AA205,"<0")

the one that doesnt is

=SUM(AA207:AA211,AA217)/COUNTIF((AA207:AA211,AA217),"<0")

Any suggestions?

Thanks

Tom

Tom:

the one that doesnt is

=SUM(AA207:AA211,AA217)/COUNTIF((AA207:AA211,AA217),"<0")


What are you trying to do? Use a condition in the sum? Than try:

=SUMIF(AA207:AA211,AA217,"<0")/COUNTIF((AA207:AA211,AA217),"<0")

I am not sure if I use correct syntax (I have spanish version). But I
believe your error is provoked in "sum(AA207:AA211,AA217)", which does not
seem to be posible.

Hope some else can help out too.

Martin

=SUM(AA207:AA211,AA217)/SUM(COUNTIF(AA207:AA211,"<0"),COUNTIF(AA217,"<0" ))


--
Regards,
Tom Ogilvy


"tom" wrote:

My spread sheet contains already some COUNTIF formula which are working
okay.

As soon as i try to change which cells are included in the formula so
they do not go in sequence i get the #VALUE! coming up

the one that works goes as follows

=SUM(AA200:AA205)/COUNTIF(AA200:AA205,"<0")

the one that doesnt is

=SUM(AA207:AA211,AA217)/COUNTIF((AA207:AA211,AA217),"<0")

Any suggestions?

Thanks

Tom

the suggested solution will not work.

Sumif and Countif will not work with discontiguous ranges as suggested.

--
Regards,
Tom Ogilvy



"Martin Los" wrote:

Tom:

the one that doesnt is

=SUM(AA207:AA211,AA217)/COUNTIF((AA207:AA211,AA217),"<0")


What are you trying to do? Use a condition in the sum? Than try:

=SUMIF(AA207:AA211,AA217,"<0")/COUNTIF((AA207:AA211,AA217),"<0")

I am not sure if I use correct syntax (I have spanish version). But I
believe your error is provoked in "sum(AA207:AA211,AA217)", which does not
seem to be posible.

Hope some else can help out too.

Martin

Thanks anyway

Can you think of any way to work around this as i cannot use the
inbetween field without alot of work due to the number of times this
calculation would be used.

Thanks

Tom

Possibly with

=Average(if(isnumer(AA207:AA1000)*(AA207:AA1000<0 ),AA207:AA1000))

Entered with Ctrl+shift+enter since this is an Array formula

If you want to average all numeric values not zero in your range (cells to
be skipped would be blank or contain text).

--
Regards,
Tom Ogilvy


"tom" wrote:

Thanks anyway

Can you think of any way to work around this as i cannot use the
inbetween field without alot of work due to the number of times this
calculation would be used.

Thanks

Tom

Great help

Thanks Very Much

Tom