SUMIF of specified cells in a range

[Bretter99]

I have a column of data that i want to use SUMIF>0 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

 

[Pete_UK]

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

 

[RaulDR]

this formula will all the positive numbers in in column A

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

 

[Castell]

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

 

[Castell]

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

 

[Bretter99]

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

 

[Castell]

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

 

[Bretter99]

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