I have a column of data that i want to use SUMIF0 but its not over the whole
range, just every 5th row for instance...

a1=5
a6=8
a11=-5
a16=2
a21=-22

SUMIF only seems to work with a continuous array of cells not targetted
cells, any ideas how to get around this? (I just need to sum the non negative
figures)
something like SUMIF(a1,a6,a11,a16,a21)0 or something like that...

Thanks

Brett

What do you have in the other cells in column A, i.e. in a2, a3, a4
etc?

If they are empty then you could use the range A1:A21.

Hope this helps.

Pete

On Apr 8, 10:49*am, Bretter99
wrote:
I have a column of data that i want to use SUMIF0 but its not over the whole
range, just every 5th row for instance...

a1=5
a6=8
a11=-5
a16=2
a21=-22

SUMIF only seems to work with a continuous array of cells not targetted
cells, any ideas how to get around this? (I just need to sum the non negative
figures)
something like SUMIF(a1,a6,a11,a16,a21)0 or something like that...

Thanks

Brett

sorry, just to clarify...

the worksheet is more like this:

A B c
customer 1 PPM Points 200
customer 1 rating 95
customer 1 performance 98%
customer 1 complaints 2
customer 1 deliveries 8
customer 2 PPM Points -25
customer 2 rating 2
customer 2 performance 22%
customer 2 complaints 11
customer 2 deliveries 15

The customer 1, customer 2's are merged into one cell and the list is 20
customers long. All i need is to sum the negative points from every 5th row
in row C to give me a total amount of negative PPM points for all 20
customers so we can see how many demerits we lost, hence i cant look at
column C as a single array, i need to cherry pick the cells to sum. Hope this
makes sense, and i'm using excel 2000 if that makes a difference.

Regards

Brett

"Pete_UK" wrote:

What do you have in the other cells in column A, i.e. in a2, a3, a4
etc?

If they are empty then you could use the range A1:A21.

Hope this helps.

Pete

On Apr 8, 10:49 am, Bretter99
wrote:
I have a column of data that i want to use SUMIF0 but its not over the whole
range, just every 5th row for instance...

a1=5
a6=8
a11=-5
a16=2
a21=-22

SUMIF only seems to work with a continuous array of cells not targetted
cells, any ideas how to get around this? (I just need to sum the non negative
figures)
something like SUMIF(a1,a6,a11,a16,a21)0 or something like that...

Thanks

Brett

Add formula in column D as follows:
=IF(AND(MOD(ROW(),5)=0,C1<0),C1,"")
The SUM column D.


"Bretter99" wrote in message
...
sorry, just to clarify...

the worksheet is more like this:

A B c
customer 1 PPM Points 200
customer 1 rating 95
customer 1 performance 98%
customer 1 complaints 2
customer 1 deliveries 8
customer 2 PPM Points -25
customer 2 rating 2
customer 2 performance 22%
customer 2 complaints 11
customer 2 deliveries 15

The customer 1, customer 2's are merged into one cell and the list is 20
customers long. All i need is to sum the negative points from every 5th
row
in row C to give me a total amount of negative PPM points for all 20
customers so we can see how many demerits we lost, hence i cant look at
column C as a single array, i need to cherry pick the cells to sum. Hope
this
makes sense, and i'm using excel 2000 if that makes a difference.

Regards

Brett

"Pete_UK" wrote:

What do you have in the other cells in column A, i.e. in a2, a3, a4
etc?

If they are empty then you could use the range A1:A21.

Hope this helps.

Pete

On Apr 8, 10:49 am, Bretter99
wrote:
I have a column of data that i want to use SUMIF0 but its not over the
whole
range, just every 5th row for instance...

a1=5
a6=8
a11=-5
a16=2
a21=-22

SUMIF only seems to work with a continuous array of cells not targetted
cells, any ideas how to get around this? (I just need to sum the non
negative
figures)
something like SUMIF(a1,a6,a11,a16,a21)0 or something like that...

Thanks

Brett

Columns C,D,E,F,etc... are for jan, feb, mar and so on so i cant really
utilise column D. I need the calc to be at the bottom of the list looking up
the column so that at the bottom of the grid of data i can put a calc in cell
'C200' for example and drag it right across 12 cells so i have the total
demerits for each month in cells C200, D200, E200, etc.... sorry if my
descriptions are not very clear....

Brett

"Castell" wrote:

Add formula in column D as follows:
=IF(AND(MOD(ROW(),5)=0,C1<0),C1,"")
The SUM column D.


"Bretter99" wrote in message
...
sorry, just to clarify...

the worksheet is more like this:

A B c
customer 1 PPM Points 200
customer 1 rating 95
customer 1 performance 98%
customer 1 complaints 2
customer 1 deliveries 8
customer 2 PPM Points -25
customer 2 rating 2
customer 2 performance 22%
customer 2 complaints 11
customer 2 deliveries 15

The customer 1, customer 2's are merged into one cell and the list is 20
customers long. All i need is to sum the negative points from every 5th
row
in row C to give me a total amount of negative PPM points for all 20
customers so we can see how many demerits we lost, hence i cant look at
column C as a single array, i need to cherry pick the cells to sum. Hope
this
makes sense, and i'm using excel 2000 if that makes a difference.

Regards

Brett

"Pete_UK" wrote:

What do you have in the other cells in column A, i.e. in a2, a3, a4
etc?

If they are empty then you could use the range A1:A21.

Hope this helps.

Pete

On Apr 8, 10:49 am, Bretter99
wrote:
I have a column of data that i want to use SUMIF0 but its not over the
whole
range, just every 5th row for instance...

a1=5
a6=8
a11=-5
a16=2
a21=-22

SUMIF only seems to work with a continuous array of cells not targetted
cells, any ideas how to get around this? (I just need to sum the non
negative
figures)
something like SUMIF(a1,a6,a11,a16,a21)0 or something like that...

Thanks

Brett

this formula will all the positive numbers in in column A

=sumif(a1:a9,"0",a1:a9)


"Bretter99" wrote:

I have a column of data that i want to use SUMIF0 but its not over the whole
range, just every 5th row for instance...

a1=5
a6=8
a11=-5
a16=2
a21=-22

SUMIF only seems to work with a continuous array of cells not targetted
cells, any ideas how to get around this? (I just need to sum the non negative
figures)
something like SUMIF(a1,a6,a11,a16,a21)0 or something like that...

Thanks

Brett

Enter the following formula in column B and SUMIF column B:
=IF(MOD(ROW(),5)=0,A1,"")


"Bretter99" wrote in message
...
I have a column of data that i want to use SUMIF0 but its not over the
whole
range, just every 5th row for instance...

a1=5
a6=8
a11=-5
a16=2
a21=-22

SUMIF only seems to work with a continuous array of cells not targetted
cells, any ideas how to get around this? (I just need to sum the non
negative
figures)
something like SUMIF(a1,a6,a11,a16,a21)0 or something like that...

Thanks

Brett

Or even this if you need to ignore negative numbers also:
=IF(AND(MOD(ROW(),5)=0,E10=0),E10,"")


"Bretter99" wrote in message
...
I have a column of data that i want to use SUMIF0 but its not over the
whole
range, just every 5th row for instance...

a1=5
a6=8
a11=-5
a16=2
a21=-22

SUMIF only seems to work with a continuous array of cells not targetted
cells, any ideas how to get around this? (I just need to sum the non
negative
figures)
something like SUMIF(a1,a6,a11,a16,a21)0 or something like that...

Thanks

Brett